Geek Answers Handbook

What is the best escape character strategy for compiling Python / MySQL?

This is my request.

cursor2.execute("update myTable set `"+ str(row[1]) +"` = \"'" + str(row[3]) +"'\" where ID = '"+str(row[0])+"'")

An error occurs when string values ​​have double quotes "some value". How to avoid all special characters?

+5
source share
4 answers

Here is an example:

import MySQLdb
column = str(MySQLdb.escape_string(row[1]))
query = "update myTable set %(column)s = %%s where ID = %%s" % dict(column = column) 
cursor2.execute(query, [row[3], row[0]])

Refresh

Here is a brief comment:

column = str(MySQLdb.escape_string(row[1]))

It is always a good idea to avoid everything that is included in the request. In this case, we dynamically add the column name and, therefore, it must be escaped before executing the query.

query = "update myTable set %(column)s = %%s where ID = %%s" % dict(column = column)

. : (1) , column, (2), , .

dict(column = column) - {'column': column}. dict.   , , (%%).

cursor2.execute(query, [row[3], row[0]])

, . , update myTable set column_name = %s where ID = %s.

+13

. ... . , ASCII 32, . , .

cursor2.execute("UPDATE myTable SET `" + str(row[1]) + "` = ? WHERE ID = ?", (row[3], row[1]))

, , , , . , . , , ; .

+7

:

colname = str(row[1]).replace("`", "\\`")
sql = "update myTable set `%s` = :col1 WHERE ID = :id" % (colname)
cursor2.execute(sql, {"col1":str(row[3]), "id":str(row[0])})
+7

When you use the Oracle MySql connector , you can avoid special characters as follows:

import mysql.connector
from mysql.connector import conversion

query = "SELECT id, code, name, description FROM data WHERE code='{:s}'"

escaped_text = conversion.MySQLConverter().escape(unescaped_text)

cursor.execute(query.format(escaped_text))
+2
source

More articles:


All Articles