Use parameterized queries to prevent SQL injection vulnerability

SQL injection vulnerabilities have been a nightmare, CMS, BBS, and blog, no suffering from a web application.

SQL injection principle

In the past, it is generally taken in the form of a spliced ??string when accessing the database, for example, when logging in, the user name and password are queried:

String SQL “SELECT TOP 1 * from [user] where username ‘” + username + “‘ and password ‘” + password + “‘”;

The value of two variables of UserName and Password is entered by the user. In the case of UserName and Password, this naturally has no problem, but the user input is untrustworthy, some malicious users can bypass the username, password login.

Suppose the value of Password is “1 ‘or’ 1 ‘1”, the value of the username is casually, for example, “ABC”, the value of the variable SQL is:

“SELECT TOP 1 * from [user] where username ‘ABC’ and password ‘1’ or ‘1’ 1 ‘”

Since ‘1’ 1 ‘is true, it is only necessary in the USER table, regardless of whether UserName, the value of Password matches, this SQL command is subject to the record. In this way, the login system is cracked.

Previous defense

There are three main ways to deal with this loan:

String detection: Limited content can only be used by routine characters such as English, numbers, if checking to user input has special characters, directly refused. However, the disadvantage is that some contents will inevitably include special characters, and this time does not reject the library. String replacement: replace the danger character to other characters, the disadvantage is that there may be a lot of danger characters, one by one replacement is quite trouble, or there may be a fish on the net. Stored procedure: Transmit the parameters to the stored procedure, but not all databases support stored procedures. If the command executed during the stored procedure is also through the splicing string, there will be a vulnerability.

Parameterized query

In recent years, since the parameterized query has appeared, SQL injection vulnerabilities have become tomorrow.

Parameterized Query or Parameterized Statement When accessing a database, use the parameter (parameter) to use the parameter when you need to fill in the value or data.

In the case of using a parameterized query, the database server does not treat the contents of the parameters to handle a part of the SQL instruction, but after the database completes the compilation of the SQL instruction, the parameter is run, so it contains instructions in even if the parameter is included. It will not be run by the database. Common databases such as Access, SQL Server, MySQL, SQLITE supports parameterized queries.

Use parameterized query in the ASP program

The parameterized query in the ASP environment is mainly completed by the Connection object and the Command object.

The Access database only supports anonymity parameters, and can be replaced by the position of the incoming parameter. Although the SQL Server database supports anonymity and non-anonymous parameters, only anonymous parameters can only be used in the ASP.

Var conn server.createObject (“AdoDb.Connection”);

Conn.connectionstring “providermicrosoft.jet.Oledb.4.0; data source” + server.mappath (“test.mdb”); ();

Var cmd server.createObject (“adoDb.command”);

CMD.ActiveConnection conn;

cmd.commandtype 1;

CMD.Commandtext “SELECT TOP 1 * from [user] where username? and password?”;

cmd.parameters.Append (cmd.createparameter (“@ username”, 200, 1, 20, “user01”);

CMD.Parameters.Append (Cmd.createParameter (“@ Password”, 200, 1, 16, “123456”);

Var rs cmd.execute ();

Response.write (RS (“UserID”). Value); rs.close ();


Use parametric queries in the ASP.NET program

The query query in the ASP.NET environment is also done by the Connection object and the Command object. If the database is SQL Server, you can use the parameters with name, the format is “@” characters plus the parameter name.

SqlConnection CONN New SqlConnection (“Server (local) SQL2005; User IDSA; PWD12345; Initial Catalogtestdb”); ();

SQLCOMMAND CMD New Sqlcommand (“Select Top 1 * from [user] where username @usrname and password @password”);

CMD.Connection Conn;

cmd.parameters.addwithValue (“UserName”, “User01”);

CMD.Parameters.addwithValue (“Password”, “123456”);

SqlDataReader Reader cmd.executeReader (); ();

INT userid reader.getint32 (0);

Reader.close ();


The parameter format of mysql is a bit different from SQL Server. Is it “?” Plus parameter name.

MySQLConnection CONN NEW MYSQLCONNECTION (“Server127.0.0.1; uidroot; pwd12345; databasetest;”); ();

MySQLCOMMAND CMD New MySQLCOMMAND (“SELECT * FROM` User` WHERE UserName? UserName and Password? Password Limit 1);

CMD.Connection Conn;

cmd.parameters.addwithValue (“UserName”, “User01”);

CMD.Parameters.addwithValue (“Password”, “123456”);

MySQLDataReader Reader cmd.executeReader (); ();

INT userid reader.getint32 (0);

Reader.close ();