Prevent SQL injection when operating MySQL in Python

The following is an article about SQL Injection found on the Internet. Recently, the project involved the prevention of SQL injection. However, because PYTHON and MYSQL are used, some of the ready-made methods provided in JAVA code cannot be used, the EXECUTE method in the MYSQLDB module does not support using placeholders for table names.

Execute(Self,Query,Args=None)

Execute a query.

Query -- string, query to execute on serverargs -- optional sequence or mapping, parameters to use with query.

Note: If args is a sequence, then % s must be used as theparameter placeholder in the query. If a mapping is used, % (key) s must be used as the placeholder.

Returns long integer rows affected, if any

Placeholders are supposed to be used for * values *, not other parts of the SQL statement. To insert table names, column names and stuff like that, use Python-level formatting.

Cur.exe cute ("select * from % s where name = % s",('T1 ', 'xx') -- python-level formatting, execution failed

Cur.exe cute ("select * from % s where name = % s"%('T1 ', 'xx') -- execute ()-level formatting. The execution is successful, but it does not prevent SQL injection.

The following is an example in the document.

To perform a query, you first need a cursor, and then you can executequeries on it:

1. C = db. cursor ()
2. Max_price =5
3. C.exe cute ("SELECT spam, eggs, sausage FROM breakfast 
4. WHERE price <% s """, (Max_price ,))

In this example,Max_price = 5Why, then, use% SIn thestring? Because MySQLdb will convert it to a SQL literal value, whichis the string '5'. When it's finished, the query will actually say, "... WHERE price <5 ".

However, manual implementation requires two steps:

1. Escape the single quotes in the variable value

2. Add single quotation marks to both ends of the variable value

#######################
It should be said that even if you do not process special characters in HTML or JavaScript, it will not have disastrous consequences. However, if you do not dynamically construct SQL statements to process special characters in variables, it may cause program vulnerabilities, data theft, data damage, and other serious security problems. There are a lot of articles on SQL Injection on the Internet. Interested readers can search for relevant materials for in-depth research.

Although SQL injection has serious consequences, you can avoid this problem by performing special character escape processing on the variables of dynamically constructed SQL statements. Let's take a classic example of a security vulnerability:

The preceding SQL statement determines whether the logon information provided by the user is correct based on the number of returned results. If the userName variable is not escaped by special characters, it is directly merged into the SQL statement, hackers can directly access the system by setting userName to "1" or '1' = '1 "without checking the user name/password.

Unless necessary, we generally recommend that you bind the PreparedStatement parameter to construct a dynamic SQL statement, because this method can avoid potential security issues of SQL injection. However, it is often difficult to avoid concatenating strings to construct dynamic SQL statements in applications. To prevent others from using special SQL characters to damage the SQL statement structure or implant malicious operations, escape the special characters before splicing the variables into SQL statements. Spring does not provide the corresponding tool class, you can do this through the StringEscapeUtils in the jakarta commons lang generic class package (spring/lib/jakarta-commons/commons-lang.jar:

Listing 4. SqlEscapeExample
 

1. PackageCom. baobaotao. escape;
2. ImportOrg. apache. commons. lang. StringEscapeUtils;
3. Public ClassSqlEscapeExample {
4. Public Static VoidMain (String [] args ){
5. String userName ="1 'or '1' = '1";
6. String password ="123456";
7. UserName = StringEscapeUtils. escapeSql (userName );
8. Password = StringEscapeUtils. escapeSql (password );
9. String SQL ="Select count (userId) FROM t_user WHERE userName = '"
10. + UserName +"'AND password = '"+ Password +"'";
11. System. out. println (SQL );
12. }
13. }

In fact, StringEscapeUtils not only provides SQL special character escape processing functions, but also provides conversion and restoration methods for special characters such as HTML, XML, JavaScript, and Java. If you do not mind introducing the jakarta commons lang class package, we recommend that you use the StringEscapeUtils tool class for special character escape processing.