On Mar 19, 2010, at 3:29 PM, David Bicking wrote:
>
>
> --- On Fri, 3/19/10, Vance E. Neff <[email protected]> wrote:
>
> <snip>
>> UPDATE table1 set (?, ?, ?) WHERE col1=? and col2=?;
>>
>> I've never used binding before but have known it is a good
>> idea in order
>> to avoid injection of bad stuff.
>>
>> Vance
>>
>
> You count the question marks from left to right.
>
>> UPDATE table1 set (<1>, <2>, <3>) WHERE col1=<4> and col2=<5>;
>
> You can also put the index number you want to use after the ? so
> they can be in any order you want.
Better still is to use a symbolic name for the parameters. The
symbolic names can be any identifier that begins with $, :, or @.
Examples:
UPDATE table1 SET col1=$c1val, co...@c2val, col3=:c3val
WHERE co...@c2val AND col3=:c3val;
You still have to translate the symbolic name into a "parameter index"
before you bind it. The sqlite3_bind_parameter_index() routine will
do that for you.
In the programs I write, I always try to use symbolic names for
parameters and I rig the infrastructure to handle the mapping from
symbolic name to parameter index. For example, if you are using the
TCL interface to SQLite, you just specify TCL variables embedded in
the SQL:
db eval {UPDATE table1 SET col1=$c1val WHERE col2=$c2val}
In the statement above, the TCL interface automatically looks up the
values of TCL variables $c1val and $c2val and binds them appropriately
before running the statement. It doesn't get any cleaner than this.
Unfortunately, other programming languages require more complex
syntax. In the implementation of "Fossil" I do this:
db_prepare(&stmt, "UPDATE table1 SET col1=$c1val WHERE col2=
$c2val");
db_bind_int(&stmt, "$c1val", 123);
db_bind_double(&stmt, "$c2val, 456.78);
db_step(&stmt);
db_finalize(&stmt);
The db_bind_int() and db_bind_double() and similar routines wrap the
sqlite3_bind_xxxxx() and sqlite3_bind_parameter_index() calls.
If we've learned one thing over the history of computing it is that
programmers are notoriously bad at counting parameters and that
symbolic names tend to be much better at avoiding bugs.
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
