Three ways to prevent SQL injection

common three ways to avoid SQL injection

One, the stored procedure

When learning about database video, it is a pre-compiled instruction stored in a database. You don't have to rewrite it when you use it, just call it right. Therefore, using it can greatly improve the execution efficiency of the program.

So, what if you create a stored program and use it? That's the problem we're going to solve today.

1. Creating a process

Programmability--drop-down menu--stored procedure--right---Query menu--Specify value of template parameter--new query--input statement--parse Check syntax in query menu is correct--execute

2. Create a specific grammar

When creating a stored program, in order to deal with the various transformations of data, usually involves a stored program with parameters, where the parameters are represented by @.

Create Procedure procedurename[: number]--[' #] represents the first of a set of stored programs, and if there is only one, this parameter can be ignored

[@parameter Data_type] [Default] [OUTPUT] [email protected] represents a parameter in the stored procedure, default represents the defaults, output indicates that the outputs value is the output value as SQLStatement--[] represents the optional parameters

3. Specific implementation process

exec[ute]procedurename [Parameter]

Example:

--Create

createprocedure scores @score1smallint, @score2smallint, @score3smallint, @secre4smallint, @score5smallint, @myAvgsmallint Output--output can be replaced by return

As Select @myAvg = (@[email protected][email protected][email protected][email protected])/5--Call procedure

[Email protected] smallint--Put the output in Avgscore

Execavgscore output 5,6,7,8,9, a stored procedure call with parameters, must be prefixed with the OUTPUT keyword, otherwise SQL will treat as a parameter

Summary: The creation of a stored program can be divided into parameters and without parameters, and contains default values and output values of the stored procedures, but they use the same principle. It's just with the output. The stored program uses the keyword output to declare the variable to be output during the call, otherwise SQL will treat it as a parameter.

Note: After creating the stored program, we can invoke the name of the stored program directly in place of the complex query statement when writing the program:

Strsql= "SELECT ...; strsql=" Execte produrename "

Second, parameterized SQL

Refers to the use of parameters (Parameter) to give values when designing a link to a database and accessing data, using an @ or, where a value or data needs to be filled in. To represent the parameter.

In the case of parameterized queries, the database server does not treat the contents of the parameter as a part of the SQL instruction, but only applies the parameters after the database has completed compiling the SQL instructions, so even if the parameter contains malicious instructions, it will not be run by the database because it has been compiled. SQL injection can be avoided to some extent.

There are some differences in the way that parameterized SQL is supported in different databases. Both are supported in SQL Server.

The basic syntax is the same on unused databases, but there are differences in the client's handwriting on different platforms, and here's an example of what SQL Server I'm learning is executing on. NET.

Parameterized SQL statements in--sql server: SELECT * from myTable WHERE MyID = @myID INSERT into myTable (C1, C2, C3, C4) VALUES (@c1, @c2, @c 3, @c4) '. Execute on. Net

SqlCommand sqlcmd = new SqlCommand ("INSERT into MyTable (C1, C2, C3, C4) VALUES (@c1, @c2, @c3, @c4)", sqlconn);

sqlcmd. Parameters.addwithvalue ("@c1", 1); ' Sets the value of the parameter @c1.

sqlcmd. Parameters.addwithvalue ("@c2", 2); ' Sets the value of the parameter @c2.

sqlcmd. Parameters.addwithvalue ("@c3", 3); ' Sets the value of the parameter @c3.

sqlcmd. Parameters.addwithvalue ("@c4", 4); ' Sets the value of the parameter @c4.

Sqlconn. Open ();

sqlcmd. ExecuteNonQuery ();

Sqlconn. Close ();

There are other ways to add parameters to the command, such as:

SQLCMD.PARAMETERS.ADD ("@c1", Sqldbtype.bigint) ' BigInt for the C1 data type

Sqlcmd.parameter ("@c1"). Value=1 ' Set value

Third, Regular Expression

For short, RES is a very powerful word verification technique. Usually when we design the program, if we want to enter a number in the text, then we will use the Isnumberic function to limit, but in many cases, for the convenience of users, we have to use more than the limited number of this technology, there are many relationships need us to follow, such as mobile phone number to be limited to 11 for, Mailbox number to limit the corresponding format and so on. This is the technique of res. It can provide a template for what we want to enter, so that the user's input must follow the format of the template, and if the format is not correct, the program cannot continue execution. This also avoids SQL injection.

For example

\d-------Representative numbers

\d{5}-------represents 5 digits

\[email protected]\w+ [email protected] before the w+ means to have at least one character, @ means that the template must have an @ character.

Of course, before using this technique, it is conditional, first of all, it needs to refer to a namespace, as follows:

Imports Re=system.text.regularexpressions.regex

That's not enough, we need a way to verify that the user input is working correctly, and here we're going to use a method of match, specifically using the following:

Dim Input,pattern as String

Input=me.txtinput.texttrim ()

Pattern=me.txtpattern.text

If re.mathc (Input,pattern). Success then ' uses the match method to validate user-entered content with a defined template

MessageBox.Show ("True,input matches pattern") Else MessageBox.Show ("False,input does not match pattern") End if

The above, by looking at. NET Video summary of the three ways to avoid SQL injection, due to the limited knowledge of expertise, the specific principle is not clear, to be later in-depth study after summary.

Three ways to prevent SQL injection