CODEWITHSUNDEEP
pythonmysqlsql
GuangshengZuo
GuangshengZuo

Reputation: 4677

mysqldb auto add ' ' to %s

Here are a piece of my python code, and the probe_id is a string : 

sql = "select flow_id from t_static_flow where probe_id = %s"                                                                                                                  
cursor = connection.cursor()                                     
cursor.execute(sql, (probe_id, ))

It works well, and but when I change the sql above to 

sql = "select flow_id from t_static_flow where probe_id = '%s'"

It gives me such error info:

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123''' at line 1")

When I add the '' surrounding the %s , It fails. But why ?

and I turn on the mysql general_log to see the sql to be executed. 

2017-07-19T13:21:37.107960Z        85 Query     select flow_id from t_static_flow where probe_id = '123'

It's my first code's sql log which works well.

but I think in the first code the final sql should be :

select flow_id from t_static_flow where probe_id = 123

how it works?

I read the MySQLdb python code . in execute function :

if args is not None:
if isinstance(args, dict):
   query = query % dict((key, db.literal(item))
       for key, item in args.iteritems())
   else:
       query = query % tuple([db.literal(item) for item in args])

I think It does not do the extra work said like @money.

Upvotes: 0

Views: 42

Answers (2)

GuangshengZuo
GuangshengZuo

Reputation: 4677

I think I catched the answer. Firstly thanks @money's help.

In MySQLdb's execute function which I mentioned in question, it uses the literal function to escape the string . In fact the literal calls a C API to solve this problem. And the real escape function is mysql_real_escape_string. In this document, I found this words:

Strictly speaking, MySQL requires only that backslash and the quote character used to quote the string in the query be escaped.

Here are the words I found in _mysql.c

string_literal(obj) -- converts object obj into a SQL string literal. This means, any special SQL characters are escaped, and it is enclosed within single quotes.

So It quotes the string in the mysql_real_esacep_string() API and return.

So In the execute 's code, the query % ... replaces the string contained the single quotes already.

same as @money said.

Upvotes: 1

Manoj-kr
Manoj-kr

Reputation: 796

As per the details, even without providing a single quote around the value, final query looks like
select flow_id from t_static_flow where probe_id = '123'
Value already has single quotes, So when you explicitly add single quotes around the value, it make it something like
select flow_id from t_static_flow where probe_id = ''123'' and breaks the query.

Upvotes: 1

Related Questions