Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of pymysql to solve the problem of sql injection. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

1. SQL injection

SQL injection is a very common method of network attack, which mainly uses parameters to cause mysql to perform unexpected operations when executing sql statements.

That is, because the passed parameters change the semantics of the SQL and become other commands, the database is manipulated.

Cause: the SQL statement uses dynamic splicing.

For example, the following code verifies user permissions by getting user information:

Import pymysqlsql = 'SELECT count (*) as count FROM user WHERE id =' + str (input ['id']) +' AND password = "'+ input ['password'] +'" 'cursor = dbclient.cursor (pymysql.cursors.DictCursor) cursor.execute (sql) count = cursor.fetchone () if count is not None and count [' count'] > 0: print ('login successful')

However, if the parameters passed in are:

Input ['id'] =' 2 or 1'

You will find that the user can log in directly to the system because the judgment condition of the original sql statement has been short-circuited by or to become the always correct statement.

Here is just an example. In fact, there are many ways to inject sql, which will not be discussed in detail here.

In short, as long as sql statements are concatenated by user input data, you must consider how to avoid SQL injection in the first place.

So, how do you prevent SQL injection?

two。 Prevent SQL injection-pymysql parameterized statements

Pymysql's execute supports parameterized sql, and the problem of sql injection can be avoided by matching parameters with placeholder% s.

Import pymysqlsql = 'SELECT count (*) as count FROM user WHERE id =% s AND password =% s'valus = [input [' id'], input ['password']] cursor = dbclient.cursor (pymysql.cursors.DictCursor) cursor.execute (sql, values) count = cursor.fetchone () if count is not None and count [' count'] > 0: print ('login successful')

This parameterized way allows mysql to avoid the existence of sql injection through preprocessing.

It is important to note that do not replace the placeholder for% sforce pymysql just because the parameter is of another type. This is not a universal placeholder for python.

At the same time, don't put quotation marks on both sides of% s just because the parameter is string, mysql will handle it automatically.

3. Prevent SQL injection-mysql stored procedure

Database stored procedures are an advanced use of mysql, but in general, database stored procedures are not recommended.

The main reasons are:

The syntax of stored procedures is too different from that of ordinary SQL statements, which increases the cost of maintenance.

Stored procedures are not common and vary greatly among databases, which makes it difficult to transplant and expand the database.

It is difficult to write, and the database scripting language is still very inconvenient to use, including the lack of many data structures, which makes a lot of things difficult to do.

Debugging is difficult, although there are some powerful IDE that provide debugging functions for database stored procedures, but usually you need to debug both at the database level and in the business at the same time, which is extremely inconvenient.

Business coupling, writing stored procedures usually need to put part of the business logic in it, which makes the business scattered in the data layer, and the coupling between the business layer and the data layer will bring great inconvenience to the project maintenance and expansion.

However, although the use of stored procedures is not recommended, it can be relied on to achieve a variety of cross-language sql injection prevention, which is useful in complex scenarios. (you need to learn it in detail later. Here is only a brief introduction.)

3.1. Stored procedure writing

Delimiter\ DROP PROCEDURE IF EXISTS proc_sql\ CREATE PROCEDURE proc_sql (in nid1 INT, in nid2 INT, in callsql VARCHAR) BEGIN set @ nid1 = nid1; set @ nid2 = nid2; set @ callsql = callsql; PREPARE myprod FROM @ callsql;-- PREPARE prod FROM 'select * from tb2 where nid >? and nid

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.