First of all, I want to thank you all for your ideas
Although it may not be the ideal solution, it works for me in this particular situation and I leave a record in case it serves someone in the future
I have solved it in the following way…
1- I create a constant and assign it the value of the sql query. The sql query has the values replaced by special words, e.g. sql_var_username, sql_var_dateofbirth… etc.
2- I transfer the value of the constant to a variable (dim sql_string as string = kconstant_value)
3- use ReplaceAll to replace special words with the corresponding value
i have a related question. maybe i have to start a new thread?
i want to manually sanitize user entered values. i suspect i have to use replaceall. wich characters do i have to delete to avoid sql injection?
Prepared Statements can actually be “easier” than what you are doing now, and safer
s="INSERT INTO mytable (values '$1','$2','$3')"
for the above you would have to “sanitize” xx1,yy2 and yyy3 if they contained any “harmful” characters or sql injection
Dim ps As SQLitePreparedStatement
s="INSERT INTO mytable (values ?,?,?)
ps = DB_PROJECT.Prepare(s)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT) // new value
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT) // new value
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT) // new value
for the above, xxx1, yyy2 and yyy3 can be any text, with no need to worry if they contained “harmful” characters, or possible sql injection data
NOTE : 1st example requires single quotes, prepared statement does not