I am in the processing of converting my old Real Studio programs to XOJO and the SQLite. In Real Studio I had to code (or replace) the apostrophe in any index field (or sorted field) so the Real studio sq. d when I’m was saving a database record. (Of course I had to converted it back when I read the database record.
My question is this. Do I still have to use this coding with the XOJO SQLite database?
I hope you can understand what I’m asking.
If a string contains apostrophe, you need to replace it by two if you are using it in a statement.
But parameterised queries save you from this and other types of SQL injection
if you do a straight query, you might be tempted to do this:
resultset = SQLSelect("SELECT * FROM table where somefield = '" + textfield.text + "'")
That would break if the text field contained an apostrophe.
This would likely work:
resultset = SQLSelect("SELECT * FROM table where somefield = '" + replaceall(textfield.text,"'","''") + "'")
but its safer to use a preparedstatement
[code] Dim ps As SQLitePreparedStatement
ps = theDb.Prepare(“select * from table where somefield =?”)
resultset = ps.SQLSelect
If I understand what you are saying I don’t need to worry about saving (changing) the apostrophe any more if I just put the “replaceall” command in the query. I can live with that.
Just 1 question. Will the “replaceall” actually change the data in the SQLite db or just in the query. I don’t think it would change any of the data in the database.
If this is true you just made my day. Because I never really understood the “Replaceall” command before.
PS: I’ll be gone for the next 5 hours so I’ll check for your answer then.
PPS: Looks like I need to figure out how the preparedstatement works never heard of it before.
Replaceall (thestring,"’", “’’”) turns all occurrences of the apostrophe into 2
Thats just for the query… if inserting, you get one apostrophe in the database
eg you insert ‘O’‘Grady’ and you get O’Grady
search for ‘The King’‘s Court’ and you get back The King’s Court
But if you use preparedstatement it also saves you from problems where your text contains ; or other reserved characters.