Re: Prepared statements?

Dawid Kuroczko wrote:
> The other question is about execute() command, so far
> I have seen two syntaxes:
>
>   execute("SELECT foo FROM bar WHERE baz=%d" % intval)
>   execute("SELECT foo FROM bar WHERE baz=%s", (intval,))
>
> ...seems like the latter is more efficient (and seems it doesn't
> like "%d").  Am I correct?  Or maybe there are other syntaxes
> that are even better?
>   

Technically speaking, the first option is more efficient, but the second
option is far preferable because of safety.

The first is more efficient, because you're essentially using the C
run-time sprintf function directly.  With the second, psycopg searches
for the substitution strings one by one, and makes the substitutions
individually.

However, when you allow psycopg to do the substitutions, it knows all of
the rules for quoting.  It will escape any characters that need
escaping, and add whatever quote marks are appropriate for the data type
of the field.  With the first option, doing the substitutions yourself,
it is way too easy to open yourself up to SQL injection attacks.

Now, if all you have is a single integer field, then there's probably no
difference, but the typical query is more complicated than that.

-- 
Tim Roberts, [email protected]
Providenza & Boekelheide, Inc.