[MySQL daily skill] deal with special characters and null values when operating MySQL database with Python

List of articles

• • 1 problem
  • 2. Solution
  • 3. Discuss

1 problem

You want to use Python ¹ Send a message constructed as a string SQL Insert statement to MySQL Server side , However, specific field values may contain special characters such as quotation marks and backslashes , perhaps NULL And other special values . Or, , You can construct SQL Statements are constructed through external input , And you want to avoid SQL The occurrence of Injection .

2. Solution

For the above problems , You can use Python Connect MySQL Driven API Placeholder mechanism or reference function provided in （ This paper focuses on the former ）, This makes the insertion of data more secure .

3. Discuss

For the problems mentioned at the beginning of this article , Here is a case . for example ：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De'Mont','1973-01-12','blue','eggroll',4);

The problem with the above case is , For fields name The value of 'De'Mont' , There is a single quote between a pair of single quotes . If you want the above SQL Statement is valid , Character escape can be realized in any of the following ways ：

• Put a single quote before the single quote that caused the problem ：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12','blue','eggroll',4);

• Put a backslash before the single quotation mark that causes the problem ：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De\'Mont','1973-01-12','blue','eggroll',4);

• take De'Mont Use a pair of double quotation marks to enclose ²：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES("De'Mont",'1973-01-12','blue','eggroll',4);

actually , If you manually construct a string form first SQL sentence , Then pass it through Python Send to MySQL Server side , Then you can manually escape characters in a way similar to the above when constructing . But the problem is , If in the above statement name The value of the field is stored in the variable , You may not know whether the value represented by the variable needs to be handled with character escape .

What's worse is , The database may also store binary data such as pictures or audio , These binary data may represent any character . What is more , The inserted value is null at the root .

besides , In the age of the Internet , The need to deal with this problem correctly is more urgent , Because it is used to construct SQL Many of the field values of the statement come from the user's form input , This will not only lead to possible values containing characters that need special processing , It may also be caused by malicious user input SQL Inject .

therefore , You must be able to use a common way to handle input in all situations .

in addition , although SQL Statement NULL Not a special character , But in the use of Python In the form of string SQL The statement is sent to MySQL On the server side , Special attention should also be paid to . stay SQL In the sentence , NULL No value . for example ： In the above case , If you don't know De'Mont My favorite color , You can put color Field set to NULL , But here's the thing , Following SQL The sentence is incorrect ：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12','NULL','eggroll',4);

Below SQL The statement is correct ：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De''Mont','1973-01-12',NULL,'eggroll',4);

Same thing , If color The specific value of comes from a Python The variables in the , Then you need to first know whether the value represented by the variable is null , To determine the structure SQL Whether the statement needs quotation marks at both ends .

actually , For the above problem of dealing with special characters and null values , You can use Python Connect MySQL Driven API Placeholder mechanism provided in . In particular , stay SQL Use placeholders... In statements , Then bind the field value and placeholder one by one . At this time , API Will do all the work you need to do ： Escape special characters or map special values to NULL .

Python Connect MySQL Of API Use %s³ The format qualifier is used as SQL Placeholders in statements . The specific use of placeholders is through the call execute() Method by passing in two parameters ：

• In string form SQL sentence , It contains the format qualifier %s ;
• A sequence of values from each field , Each value corresponds to the placeholder one by one .

cursor = conn.cursor()
cursor.execute(''' INSERT INTO profile (name,birth,color,foods,cats) VALUES(%s,%s,%s,%s,%s) ''', ("De'Mont", "1973-01-12", None, "eggroll", 4))
cursor.close()
conn.commit()

In the above case, we can also see , If necessary, it means that the value is NULL , So in Python in None Bind with placeholders .

Final , from execute() Method to MySQL Server side SQL The statement is in the following form ：

INSERT INTO profile (name,birth,color,foods,cats)
VALUES('De\'Mont','1973-01-12',NULL,'eggroll',4)