On Wed, Jul 04, 2012 at 09:06:16AM -0400, Igor Tandetnik scratched on the wall: > Nico Williams <n...@cryptonector.com> wrote: > > SQLite3 also needs to know the identifiers of schema elements at > > statement prep time. It might be nice to have a variant of > > sqlite3_prepare_v2() that takes a varargs list of parameters which > > must be identifiers, and then have a syntax for referring to > > identifier parameters as opposed to value parameters. > > That doen't make much sense.
Yes it does. The identifiers would be baked into the statement before it is prepared. He's not trying to alter the identifiers after the statement is prepared, he's trying to prevent SQL-injection attacks while the statement is prepared. The #1 rule to prevent SQL-injection attacks is, "Never let user-generated strings pass through the SQL parser". Statement re-use is nice, but the real value in using SQL statements and parameters is that you avoid passing data values through the SQL parser, making SQL-injection attacks impossible. 99% of SQL-injection attacks could be avoided if the programmer had only used SQL parameters rather than string concatenation. It's also why sqlite3_exec() should really include a vararg option, so that one could pass in values outside the SQL string itself. Unfortunately, you can't use parameters for everything. If you get in a situation where you need to use a user-defined table name, parameters won't help you. The (distant) #2 rule to prevent SQL-injection attacks is, "Sanitize user-generated strings before they pass through the SQL parser." The issue with that is that too many programmers think themselves clever and smart, so they write their own sanitizer, and they do so poorly. Hence the popularity of "tried, true, and tested" string sanitizers being built into SQL libraries. That's what Nico is looking for. Thankfully, SQLite includes this functionality, just not in the _prepare() functions. SQLite supports several extensions to the standard printf() syntax in the sqlite3_*printf() family of functions. Both %q and %Q can be used for values, while %w can be used for identifiers. The sqlite3_*printf() functions will properly quote and sanitize any value in the generated string. There is also a %z and %p, but they're not really important for this discussion. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
