[Announce] libzdb 2.1

[Jan-Henrik Haukeland]

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.