[Tutor] Didn't take long to hit my next wall!

[Kent Johnson]

Brian Gustin wrote:
> python in this form uses BIND variables..
>  >>query = '''UPDATE cost_grid
>  >>               SET cost_1 = %s <--- %s = the bind variable 
> placeholder/formatter
>  >>               WHERE cost_grid_id = %s
>  >>               AND finish_dro = %s''''  % ( a,c,b) <--- the raw tuple
> 
> 
>   That is, what is provided to python in the tuple following , is 
> formatted as specified by the %s , and as such, is a formatted string 
> (special characters are properly esscaped), and as far as sql query is 
> concerned, it is escaped safely..

No. There are two ways to write this that look very similar but act very 
differently. To simplify the example a bit, suppose the query is
query = '''update cost_grid set cost_1 = %s where cost_grid_id = %s'''
and the values for cost_1 and cost_grid_id are in variables a and b.

If you write
cursor.execute(query % (a, b))

then you are using string formatting to put the values into the query. 
This is problematic when a or b needs to be escaped in any way, and is 
open to SQL injection attacks.

On the other hand, if you write
cursor.execute(query, (a, b))

then the values are passed as a separate parameter to execute(). In this 
case the DB wrapper will properly escape the values.

Kent