SQL Injection solution (C # example ),

SQL Injection solution (C # example ),

Recently encountered an SQL Injection Protection Problem in programming. Here, we will summarize SQL injection. Take MYSQL (5.1) as an example.

I. general SQL statements

Observe SQL statements

SELECT * FROM [tableName] WHERE col1='value1'

This statement consists of three parts. The SELECT clause filters the results. The FROM clause specifies the filtering range and the WHERE clause specifies the conditions. If the following replacement is performed:

SELECT * FROM [tableName] WHERE 1

Then, all contents in the [tableName] Table are selected. It is not difficult to analyze its syntax to find that WHERE evaluates its true value expression and then serves as a filter condition.


Ii. Understanding of the WHERE clause now (in MYSQL5.1) Test the following statements

SELECT * FROM [tableName] WHERE false or 1=‘1’ and '1'='1' and "1"="1"

We also get all the content of the [tableName] table.
This test illustrates two points:
1. WHERE calculates the true value of the following expression
2. Without symbols, 'and "can all pass the true value test, which may not be a problem with some special symbols.


3. The WHERE clause is usually used to construct a WHERE clause by concatenating strings. Generally:

SELECT * + FROM [tableName] + WHERE col1=' + value1 + ' 

Value1 is often obtained from input. However, when the WHERE clause has a certain feature in (2), there may be exceptions. If you construct a WHERE clause by splicing, the WHERE clause may be displayed in the following format:

SELECT * FROM [tableName] WHERE col1='badvalue' or 1='1'

That is to say, if you are familiar with SQL statements and have carefully constructed an input {badvalue 'or 1 = '1} containing special SQL characters, the whole table will be returned.
When programmers adopt "work, there are similar:

SELECT * FROM [tableName] WHERE col1="badvalue" or 1="1"

{Badvalue "or 1 =" 1} In }.
In short, once the programmer directly concatenates user input into an SQL statement, there is a risk of stealing the true WHERE value. In this way, the user changes the application logic by simply changing the terminal input, which is called injection.


4. Observe the content in the braces above, which still exists in MYSQL (5.1:

SELECT * FROM [tableName] WHERE col1='badvalue' ; select * from [another] where 1='1'

That is, enter {badvalue'; select * from [another] where 1 = '1 }. You can replace select in parentheses with drop, update, and so on. The premise is that the programmer adds user input to the SQL query statement without any processing.
You can make a simple conclusion that special SQL characters are nested in concept. However, due to the use of direct concatenation in processing, users are involved in writing SQL statements, enter special characters to truncate the SQL statement, leading to unexpected results.


5. There is no need to make it difficult to solve the problem. However, the solution is usually controversial.
The longest language I use is C #. For some reason, the first language I carefully studied is C #. Here is a solution for C.


6. The conception and deployment of the solution mainly aims to isolate the right of user input to "build" SQL statements. User input can only be provided as data. As long as this principle is met, the assembly of the WHERE clause is clear.
We can try our best to write all the non-user input, and then escape all the special characters entered by the user. In this way, the Where function is illustrated.

/*** AffirmNodePair example: * {"or col1 =", "value1"} * {"and col2> ", "value2"} **/public string Where (Dictionary <string, string> affirmNodePair) {StringBuilder builder = new StringBuilder (); // preprocessing builder. append ("where 1"); foreach (var item in affirmNodePair) {builder. append (item. key); builder. append ("'"); // filter user input: Escape special characters entered by the user into foreach (var c in item. value) {switch (c) {case '\ '': builder. append (@ "\"); break; case '\ "': builder. append (@ "\"); break; case ';': builder. append (@ "\"); break; default: break;} builder. append (c);} builder. append ("'"); builder. append ("");} return builder. toString ();}

The Where clause is similar to the following:

Where 1 or col1='value1' and col2>'value2'

After {badvalue' or 1 = '1} is entered, the escaped result is obtained.

Where 1 or col1='badvalue\' or 1=\'1'

Errors can be reported as expected in MYSQL (5.1) I tested. The specific deployment should be based on the actual situation. Limited to working conditions, only a basic idea is provided here. Thank you for your patience.