SQL Injection prevention using parameterized Query

Document directory

• Principles of SQL Injection
• Previous defense methods
• Parameterized Query

The SQL injection vulnerability was once a nightmare for Web applications, and none of CMS, BBS, and Blog were affected.

Principles of SQL Injection

In the past, Web applications generally usedConcatenated stringFor example, the user name and password are used for query during logon:

string sql = "SELECT TOP 1 * FROM [User] WHERE UserName = '" + userName + "' AND Password = '" + password + "'";

The values of the userName and password variables are input by the user. When userName and password are both valid, this is naturally no problem, but user input is untrusted. Some malicious users can log on without the userName and password by using some techniques.

Assume that the password value is"1 'or '1' = '1", The userName value can be obtained at will, for example," abc ". The value of the variable SQL is:

"SELECT TOP 1 * FROM [User] WHERE UserName = 'abc' AND Password = '1' or '1' = '1'"

Because '1' = '1' is always true, as long as there is data in the User table, no matter whether the values of UserName and Password match, this SQL command will be able to find records. In this way, the logon system is cracked.

Previous defense methods

In the past, there were three main methods to deal with such vulnerabilities:

• String Detection: The restricted content can only contain regular characters such as English letters and numbers. If the user enters a special character, the content is rejected directly. But the disadvantage is that some of the content in the system may inevitably contain special characters. At this time, you cannot refuse to store the data in the database.
• String replacement: Replace dangerous characters with other characters. The disadvantage is that there may be many dangerous characters. It is quite troublesome to replace one-to-one enumeration, or there may be a fish in the Internet.
• Stored Procedure: Pass the parameters to the stored procedure for processing, but not all databases support the stored procedure. If the commands executed in the stored procedure are also concatenated with strings, the vulnerability persists.

Parameterized Query

In recent years, since the emergence of parameterized queries, the SQL injection vulnerability has become increasingly difficult.

Parameter Query (Parameterized Query or Parameterized Statement) is a Parameter that is used to provide a value when a database is accessed, where a value or data needs to be filled in.

When parameterized query is used, the database server does not treat the parameter content as a part of the SQL command. InsteadThe database runs the SQL command only after it has compiled the SQL command.Therefore, even if the parameter contains commands, it will not be run by the database. Common databases such as Access, SQL Server, MySQL, and SQLite support parameterized query.

Use parameterized query in ASP program

Parameterization query in ASP environment mainly consistsConnection objectAndCommand objectComplete.

The Access database only supports anonymous parameters and uses question marks instead of input parameters. Although the SQL Server database supports anonymous and non-Anonymous parameters, only anonymous parameters can be used in ASP.

var conn = Server.CreateObject("ADODB.Connection");
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Test.mdb");
conn.Open();

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();
conn.Close();

Use parameterized query in ASP. NET programs

In the ASP. NET environment, query is also performed by the Connection object and Command object. If the database is SQL Server, you can use a parameter with a name. The format is"@" Followed by parameter name.

SqlConnection conn = new SqlConnection("server=(local)\\SQL2005;user id=sa;pwd=12345;initial catalog=TestDb");
conn.Open();

SqlCommand cmd = new SqlCommand("SELECT TOP 1 * FROM [User] WHERE UserName = @UserName AND Password = @Password");
cmd.Connection = conn;
cmd.Parameters.AddWithValue("UserName", "user01");
cmd.Parameters.AddWithValue("Password", "123456");

SqlDataReader reader = cmd.ExecuteReader();
reader.Read();
int userId = reader.GetInt32(0);

reader.Close();
conn.Close();

The parameter format of MySQL is different from that of SQL Server."?" Add parameter name.

MySqlConnection conn = new MySqlConnection("server=127.0.0.1;uid=root;pwd=12345;database=test;");
conn.Open();

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();
reader.Read();
int userId = reader.GetInt32(0);

reader.Close();
conn.Close();

Address: http://heeroluo.net/ShowPost61.aspx