CODEWITHSUNDEEP
pythonsqlmysqlstringliterals
Charles
Charles

Reputation: 21

Python SQL DB string literals and escaping

Anyone know if the MySQLdb will automatically escape string literals for SQL statements?

For instance I am trying to execute the following:

cursor.execute("""SELECT * FROM `accounts` WHERE `account_name` = 'Blah'""")

Will this escape the account name automatically? Or will it only escape if I do the following?:

x = 'Blah'
cursor.execute("""SELECT * FROM `accounts` WHERE `account_name` = %s""", (x))

Or will it do it for both? Can anyone clarify this as I can't find any information on it.

Upvotes: 2

Views: 3067

Answers (2)

nonot1
nonot1

Reputation: 2818

Escaping is only done when you give the query and data to MySQLdb separately. That's how it knows what to escape. :-)

Thus, only your 2nd example will escape:

x = ('Blah',)
cursor.execute("""SELECT * FROM `accounts` WHERE `account_name` = %s""", x)

Note how I changed x to to tuple. That is what MySQLdb expects. It sort of makes sense since you may need to pass in multiple variables. Like:

x = ('Blah','Foo23')
cursor.execute("""SELECT * FROM `accounts` WHERE `account_name` = %s OR `account_code` = %s""", x)

Let me know if this answers your question.

Good Luck. :-)

Upvotes: 1

bobince
bobince

Reputation: 536379

There is no escaping in the first example, it is a raw SQL query. It's valid, it'll work, but obviously it only makes sense if you always want to search for account Blah.

When you need to get an account from a name in a variable, you will need the parameterised version. However your example may not work as expected as (x) isn't a tuple, it's just the value x. x in a tuple sequence would be (x,). To avoid confusion you may prefer to use the list [x].

Upvotes: 2

Related Questions