Hi,
following advices, I get away from InsertRecord and use DatabaseRecord to add a new Record and avoid potential troubles with '.
is there any other character (like ') that Xojo do not like ?
Consider using Prepared Statements. Visit http://documentation.xojo.com/index.php/SQLitePreparedStatement for the details.
like Frederick said, Prepared statements is your best bet. If you can not or do not want to go down that route, you will need to SQL-ize the quotes and other special characters by hand to make sure you dont run into issues.
[quote=43205:@Emile Schwarz]Hi,
following advices, I get away from InsertRecord and use DatabaseRecord to add a new Record and avoid potential troubles with '.
is there any other character (like ') that Xojo do not like ?[/quote]
my advice would be to move away from using raw sql
To do INSERTS I’d move to insert record OR better yet prepared statements
For updates I’d use prepared statements
Then you can avoid the odd characters and also possible sql injection attacks/errors
I use the following function for this:
[code]Function EscapeSQLData (data As String) as String
// Prepare a string for use in a SQL statement. A string which is being
// placed into a SQL statement cannot have a single quote in it since that will
// make the database engine believe the string is finished.
// For example the word “can’t” will not work in SQL because it will see the word
// as just “can”.
// In order to get around this you must escape all single quotes by adding a second
// one. So “can’t” will become “can’'t” and then SQL command will work.
// Replace all single quotes with two single quote characters
data = replaceAll( data, “'”, “‘’” )
// Similar issues occur with & so we double it in the statement
data = replaceAll( data, “&”, “&&” )
// Return the new data which is ready to be used in SQL
return data
End Function[/code]
Call it with the string as the parameter and use the returned string in the SQL statement.
Thanks all.