Update: since 2.3.3 dbmail uses prepared statements all over using libzdb's API unless input is trusted

On June 16, 2006, Timo Sirainen wrote:

I've been thinking about an API for SQL handling that's easy and safe to use in C where you just can't cause accidental SQL injection holes to happen without trying hard. Or I hadn't really thought much about it until I read your post now. I think something like this would work nicely:

1. For simple SQL queries you could use just:

sql_query(db, "SELECT foo FROM table WHERE col = %s AND col2 = %s", s1, s2);

So all %s occurances are automatically escaped here and '' added.

2. For more complex queries where you need to get some columns or table names from variables:

// register fields before they can be used as unescaped placements within queries.
// could also check that contents are valid so even by changing these field names
// in config file you couldn't cause SQL injections
sql_fields.field1 = sql_register_field(db, "field1");
sql_fields.field2 = sql_register_field(db, "field2");

query = sql_query_new(db);
sql_query_add(query, "SELECT %u FROM table WHERE ", sql_fields.field1);
sql_query_add(query, "WHERE %u = %s", sql_fields.field2, userdata);

GCC's -Wformat would then take care of warning of all wrong uses.

Aaron Stone wrote in response:

Add a simple API for prepared statements and bound variables, and we're golden.

There should also be some smart handling of potentially long queries, for example an IN (…, …, …, …) setup with potentially hundreds of values in the IN list.

safer_sql.txt · Last modified: 2012/02/27 21:45 by bas
DBMail is developed by Paul J Stevens together with developers world-wide