Just To Be Sure...MySQL

[Christian Heimes]

> A lister recently responded to my post concerning mysl commands of the
> following type:
>
> cursor.execute('insert into foo values (%s, %s)' % (bar, something))
>
> stating that I need to eliminate the "%" to prevent injection attacks, thus:
>
> cursor.execute('insert into foo values (%s, %s)', (bar, something))
>
> My question is simply this: Is that advice good for *all* mysql commands? Or
> are there some where the "%" is necessary and a comma would fail? I need to
> update lots of mysql commands. If I can do it without harmful consequences,
> I'll do it across the board. Otherwise, I'll have to test each one.
> TIA,
> beno

You *MUST NOT* use string formatting for SQL commands unless you 
carefully quote and validate the strings. Otherwise your SQL application 
is vulnerable to SQL injection attacks. SQL injections are one of the 
most common and devastating attacks for web applications these days.

Example:
"Select * from Users where uid = %s" % uid
uid = "1; DROP Table users;"

Guess what happens here ...


So yes, you must use the special syntax for all your commands. The DBA 
takes care of quoting. But you can't use the % replacement character for 
anything than the variable part of a DQL or DML statement. Variable 
parts are the right side of a WHERE, HAVING, SET and (IIRC) ORDER BY 
clause and the body of a VALUES block. But you can't do "Select * FROM %".

Christian