ADO. NET Quick Start-SQL injection attacks

ADO. NET Quick Start-SQL injection attacks
Related Knowledge: You can construct an SQL command string through String concatenation. However, the concatenation of SQL command strings is an important cause of "SQL injection attacks. Consider the following example: retrieve the category information with the Name "Bikes" from the ProductCategory table. (The example database uses the Redbook database AdventureWorks_WroxSSRS2012)
If you want to use a string, it will be written:

string name = "Bikes";     string strCmd = "SELECT ProductCategoryID, Name FROM Production.ProductCategory WHERE Name='" + name + "'";

Note: single quotes are the start and end marks of strings.

However, if the name variable is not hardcoded by the program but input by the user (for example, from the input box on the page), it may be "invalid" input. For example:

string name  = "Bikes'; DELETEFROM Production.ProductCategory;'";     string strCmd = "SELECT ProductCategoryID, Name FROM Production.ProductCategory WHERE Name='" + name + "'";

Note: A single quotation mark is added after Bikes "... Name = '"+ name +"' "constitutes a legal SQL statement and is executed as follows:

"SELECT ProductCategoryID, Name FROM Production.ProductCategory WHERE Name='Bikes';DELETEFROM Production.ProductCategory;";

In this case, strCmd first executes the SELECT statement and then the DELETE statement.

This situation is extremely dangerous. The root cause is that single quotes are used as the starting and ending mark of strings. strings that are illegally entered by the program are concatenated with SQL strings, which poses a serious threat to the database. This is called an SQL injection attack.
Because the injection attack is caused by single quotes, it is natural to mitigate the problem by not interpreting single quotes as "starting and ending characters of strings", but simply as single quotes.
In SQL, to represent a single quotation mark, use :''. (This is not a dual citation, but two single cited connections .)
Therefore, if you replace all single quotes in the command string with two single quotes, You can effectively reduce SQL injection attacks:

string strCmd = "SELECT ProductCategoryID, Name FROM Production.ProductCategory WHERE Name='" + name + "'";     string strCmdEncoded = strCmd.Replace("'", "''");

 

Sample Code:

Staticvoid Main (string [] args) {string userName = "xxx"; string password = "xxx 'OR '1' = '1 "; // construct a string that may generate SQL injection attacks string strCmd = "SELECT AccountID FROM Account WHERE AccountName = '" + userName + "' AND Password = '" + password + "'"; // The following statement replaces single quotes with two single quotes so that it does not represent the start and end of the string, thus eliminating SQL injection attacks. // strCmd = strCmd. replace ("'", "'' "); string strConn = @" server = Joe-PC; database = AccountDBforSQLInjection; uid = sa; pws = root "; SqlConnection conn = new SqlConnection (strConn); conn. open (); SqlCommand cmd = new SqlCommand (strCmd, conn); SqlDataReader dr = cmd. executeReader (); if (dr. read () {Console. writeLine ("Logon successful! ");} Else {Console. WriteLine (" the user name or password is incorrect! ");} Conn. Close ();}

 

Program Analysis: If the userName and password match in the data, the corresponding AccountID is returned, indicating that the login is successful. If the user does not match, the login fails. However, after designing an SQL Injection Attack String (see the sample code), No matter what user name and password are entered, the login will be successful. Cancel strCmd = strCmd. replace ("'", "'' ");. When you run the program again, an SQL exception is thrown. This indicates that the SQL statement is considered to be invalid, SQL injection attack failed.