Python’s MySQLdb Parameter Placeholders

Using “MySQLdb” API for Python is very handy and also recommended to use String Formatted Parameterized placeholders, because when you pass them as the “execute” ‘s function operands, the library does the string formatting for you and also does the escaping to prevent [Basic] SQL injections.
But there are two notes to consider when you’re using them :

1 – pass the params as a tuple

You have to always pass the params as a tuple to the execute function. So the following is not valid :

max_value = 5
sql = 'SELECT * FROM my_table WHERE Score = %s '
my_cusror.execute(sql , max_value)

Beacuse the max_value is not being passed as tuple. So you have to pass it like this :

my_cusror.execute(sql , (max_value,) )

You have to enclose it with Parenthesis and more important than that you have to have a “,” at the end. Because only having the Parenthesis doesn’t enforce it to a tuple, while the last “,” does.
Obviously if you have more than one parameter to send, you won’t need it as you already have some “,” in place.

my_cusror.execute(sql , (max_value,minvalue) )

2 – Placeholders can only be used to insert column values

According to the documents :

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.

Actually I figured this out the hard way, I was trying to join a series of IDs and pass it as one Parameter to the SQL :

sql = 'SELECT * FROM my_table WHERE ID IN (%s)'
my_cusror.execute(sql , ','.join(map(str,IDS)))

But I’m only getting the result for the first ID. Even this didn’t work :

sql = 'SELECT * FROM my_table WHERE ID IN (%s)'
ids_str = ','.join(map(str,IDS))
my_cusror.execute(sql , ids_str)

But this works like a charm :

sql = 'SELECT * FROM my_table WHERE ID IN (%s)' % ','.join(map(str,IDS))

So I eventually learned that :

Parameter placeholders can only be used to insert column values. They can not be used for other parts of SQL, such as table names, statements, etc.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s