On 21. feb.. 2008, at 09.23, Paul J Stevens wrote:
One of the things that I'm still unsure about is string escaping on untrusted input values.
dbmail does a lot of 'INSERT INTO table (f1,f2,f3) VALUES (long, long, _escaped_string_)
where the escaped strings are always put through the proper string escaping calls in the client lib of the selected backend.
As I understand things, for inserts this can be solved by using prepared statements (which is part of what I really like about libzdb).
Yes, the right way to do this is to use a prepared statement. This has been the general case since prepared statement was invented. It is the only way to be really safe against sql injection. And you do not have to escape a string used in a prepared statement.
However, if you control the SQL string yourself you should use Connection_execute(Query) instead as it is more lightweight.
But how about:
SELECT * FROM table WHERE f1 LIKE '%somestring%';
where again, somestring is totally untrusted user input.
can I rewrite that code to do
SELECT * FROM table WHERE f1 LIKE ?
and bind the argument to somestring with the globbing char tacked onto both ends?
Yes, something like this,
PreparedStatement_T p = Connection_prepareStatement(con, "select * from table where f1 like ?;"); PreparedStatement_setString(p, 1, "%blabla%"); ResultSet_T result = PreparedStatement_executeQuery(p);
There is a minor quirk though with SQLite when using PreparedStatement_executeQuery(), if the Connection is used for further statements before it is returned to the pool, you must call Connection_clear() as stated in the latest change log.
* API: Connection_clear() is exposed as a public method. Normally it is not necessary to call this method, but in some situations, if you use PreparedStatement_executeQuery it is necessary to call this function to close a prepared statement explicit. Basically, if you see this SQLite error, "SQL statements in progress", call this function to close any previous opened statements before proceeding.
Further releases my refactor away this quirk.