current position:Home>[MySQL daily skill] deal with special characters and null values when operating MySQL database with Python

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

2022-05-15 06:15:13TakingCoding4Granted

1 problem

You want to use Python 1 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 2
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 %s3 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)

  1. You may need to refer to 【MySQL Daily skill 】 Use Python Connect 、 choice 、 Disconnect database

  2. In this case, ensure that the database ANSI_QUOTES SQL Mode is not in effect .

  3. If you want to use... In a value % Literal , You need to in SQL Use in statement %% .

copyright notice
author[TakingCoding4Granted],Please bring the original link to reprint, thank you.
https://en.pythonmana.com/2022/131/202205110057409411.html

Random recommended