Handling of single quotes and some special characters in SQL

To prevent program SQL statement errors and SQL injection, single quotes must be processed. There are 2 ways to:

1, use parameters, such as SELECT * from yourtable WHERE name = @name;

In Java, you use preprocessing PreparedStatement to add parameters.

2, if you do not use parameters, and string concatenation, the single quotation mark must be judged and replaced, in the database, with 2 single quotation marks to represent 1 actual single quotes. So, if it is stitching, you need to use String.Replace ("'", "" ") to replace the 1 single quotes with 2 will be no problem.

I. SQL injection and its basic knowledge of prevention
As you may all know, SQL injection is primarily an inspection vulnerability that takes advantage of character-type parameter input.
For example, there are queries in the program:
String sql = "SELECT * from SiteUsers WHERE username=" + UserName + "";
The username parameter is entered from the user interface.
If it is a normal input, such as "Peter," the SQL statement is threaded into:
"SELECT * from SiteUsers WHERE username=peter";
If the attacker enters the following string:
"XXX; DROP TABLE siteusers WHERE 1=1 or username=xxx "
The SQL statement will now look like this:
"SELECT * from SiteUsers WHERE username=xxx; DROP TABLE siteusers WHERE 1=1 or username=xxx ";
As a result, two SQL statements are executed, and the consequence of the second statement is more serious.
The method of preventing injection is very simple, as long as the user input of the single quotation marks into a double-line:
String sql = "SELECT * from SiteUsers WHERE username=" + username.replace ("", "") + "";
Thus, if you enter the above malicious parameter, the entire SQL statement will become:
"SELECT * from SiteUsers WHERE username=xxx; DROP TABLE siteusers WHERE 1=1 or username=xxx ";
An SQL statement is executed, and the entire bold part becomes the parameter value.
The general practice is to call the following common functions in the program to process the parameters:
private string Safesqlliteral (String inputsql)
{
Return Inputsql.replace ("", "" ");
}
Since many people would neglect this single-quote substitution, it is really safe to use parameterized queries.
Two. Parametric query
In ADO, we provide a parameterized query method, which can replace the above approach of splicing SQL statements.
The specific implementation of parameterized queries is:
(1) Organize an SQL statement with parameter names as the CommandText of SqlCommand.
(2) Use the Parameters.Add method to set the parameter value.
(3) Execution of SqlCommand. (This step is the same as the one in which SQL is spliced.) ）
Here is an example:
String sql = "Select T2.dep_code, t2.dep_name from DEP";
SQL + = "WHERE t2.dep_name like (%+ @Param +%) ";
SqlCommand SqlCommand = new SqlCommand (SQL,CN);
SQLCOMMAND.PARAMETERS.ADD (New SqlParameter ("Param", s));
The @param is the parameter name, and S is the user input query condition string.
(Incidentally: The Oracle Query statement parameter is represented by a question mark, not the "@ Parameter name" form.) ）
Using this parameterized Query method, the task of preventing SQL injection is given to ADO.
If a parameterized query must be used uniformly in a project, there is no need to worry about SQL injection vulnerabilities caused by individual programmer negligence.
However, the problem is not complete, the vulnerability of SQL injection is blocked, but the correctness of the query results, parameterized query can not help any of the busy.
Three. Wildcard issues
If you use a like statement to make a fuzzy query, there are some special wildcard problems.
The wildcard characters for SQL Server include underscores (_) and percent semicolons (%), which represent a single character and any number of characters, respectively.
If the user input parameters include these wildcard characters, an incorrect result occurs.
For example:
WHERE t2.name like (%+ @Param +%)
If the user enters an underscore, he expects the result to be a record with an underscore in the Name field, but the result is that all records will be queried. The same is true for input percent signs.
To do this, you must escape the wildcard character before passing in the user input as a parameter value, that is, if the user enters a query condition that contains wildcards, they must be treated as data instead of wildcards.
In SQL Server query statements, the method of escaping to normal data is enclosed in square brackets.
For example, if you want to query an underlined field, the correct wording is:
WHERE t2.name Like (%+ [_] +%)
Similarly, if you want to query a field with a percent semicolon, the correct wording is:
WHERE t2.name like (%+ [%] +%)
Therefore, even with parameterized queries, the following processing must be performed before the user input is passed into the sqlcommand.parameters as a parameter value:
s = s.replace ("%", "[%]");
s = S.replace ("_", "[_]");
Four. Square brackets problem
If you are careful enough, you may find that there is a problem with square brackets.
Since the square brackets are used to define the contents of the data, what happens if the user enters a query parameter that itself includes square brackets?
Depending on the user's expectations, if you enter a square bracket, the query results should include only those records that contain square brackets in the field values.
However, the experimental results show that if there is no single left parenthesis paired, the left bracket is ignored when queried.
In other words, the following statement:
WHERE t2.name like (%+ [+%)
is equivalent to the following statement:
WHERE t2.name like (%+ +%)
This causes all records in the table to be included in the query results, as if there were no filtering conditions.
To do this, the left parenthesis must also be escaped if the user enters a query condition that contains the left parenthesis:
s = S.replace ("[", "[[]");
Note: The right bracket does not have this problem.
Five. Other precautions
As Microsoft recommends, any input that could lead to a problem can be checked and rejected in the UI section.
These suspicious inputs include:
Semicolon (;): A delimiter between multiple query statements, which is often the second query statement when an injection attack is a malicious query.
Single quote (): String data delimiter, which is most dangerous, has been discussed earlier.
Annotation (– or/*,*/): Some databases can use annotations to set the behavior of some query engines, such as how to use indexes.
XP_: Extending the prefix of stored procedures, after the SQL injection attack succeeds, attackers will often execute extended stored procedures such as xp_cmdshell, gain system information, and even control and destroy the system.
Vi. Conclusion
Two things should be done to prevent SQL injection and to avoid the problem of inaccurate query results when users enter special characters:
(1) using parameterized queries.
(2) before setting the query parameter value using the user input string data, first call the following common handler function:
private static string Convertsql (String sql)
{
sql = SQL. Replace ("", ""); Ado. NET has done, do not do it yourself
sql = SQL. Replace ("[", "[[]"); This sentence must be preceded by the following two statements, otherwise the brackets as escape characters will be treated as data again
sql = SQL. Replace ("_", "[_]");
sql = SQL. Replace ("%", "[%]");
return SQL;
}

Handling of single quotes and some special characters in SQL