Three main ways to avoid SQL injection

To say that SQL injection should start from the perspective of. NET video, we have heard that in the process of program development, we often encounter SQL injection problem, that is, the instruction hidden code attack. Specific principles in the end is how the matter, looked up some information seems to be involved in the compilation principle, also did not understand, just the video said that these three methods are often used to avoid SQL injection of the most commonly used methods, so look for some information to learn about the knowledge has a certain understanding. Here is a simple introduction to how these three methods are used in particular.

One, the storage program

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, how do you create a stored program and use it? This is the problem we are going to solve today.

1. Creating a Process

Programmability--drop-down menu--stored procedure--right---Query menu ——— Specify values for template parameters-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]--[:number] represents the first number of a set of stored programs, and if there is only one, this parameter can be ignored [@parameter  data_type] [default] [OUTPUT]   [e-mail protected] represents a parameter in a stored procedure, default represents the defaults, and output indicates the outputs value is the output value Assqlstatement   --[] represents an optional parameter

3. Specific implementation process

Exec[ute] procedurename [parameter]

Example:

--Create Createprocedure Scores@score1smallint, @score2smallint, @score3smallint, @score4smallint, @score5smallint, @ Myavgsmallint Output    --output can replace as select@myavg= with return (@[email protected][email protected][email protected][ Email protected])/5--call process [email protected] smallint      --Put the output in Avgscore execavgscore output 5,6,7,8,9,   -- Stored procedure calls 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 outputs are worth the stored program, but they are used 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= "Execute procedurename;"

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 = @myIDINSERT to MyTable (C1, C2, C3, C4) VALUES (@c1, @c2, @c3, @c4)

On. NET Execute 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 is the C1 data type Sqlcmd.parameter ("@c1"). Value=1     ' SetPoint

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

\w+@\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 Stringinput=me.txtinput.texttrim () pattern=me.txtpattern.textif  re.mathc (Input,pattern). Success then ' uses the match method to validate the user-entered content with the defined template      MessageBox.Show ("True,input matches pattern") Else       

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.