How does an SQL Injection happen?

Question

Why creating parameters for each field that will be updated helps prevent SQL Injection? Example:

SqlCommand comm = new SqlCommand("UPDATE Contatos Set Telefone = @Telefone, " +
                             "Cidade = @Cidade, " +
                             "Email = @Email, " +
                             "Endereco = @Endereco " +
                             "WHERE Nome = @Nome", conn);
comm.Parameters.AddWithValue("@Telefone", txtTelefone.Text);
comm.Parameters.AddWithValue("@Cidade", txtCidade.Text);
comm.Parameters.AddWithValue("@Email", txtEmail.Text);
comm.Parameters.AddWithValue("@Endereco", txtEndereco.Text); 
comm.Parameters.AddWithValue("@Nome", txtNome.Text);

Answer 1

How an SQL Injection Happens?

Let’s change your example a little bit:

SqlCommand comm = new SqlCommand("UPDATE Contatos Set Telefone = " + txtTelefone.Text +  " " +
                         "WHERE Nome = @Nome", conn);

Suppose now I type in txtTelefone the following command:

0 where 1=0; drop database MeuSistema;

Depending on the user’s permission, you agree that the entire database can be deleted?

That is to say SQL Injection. It is usually not used to erase databases, but to include false information to hijack the system or alter its behavior.

This is quite common in frameworks weak and open source. There are sources of information on the Internet specializing in attacks on certain frameworks. Closed source can also happen.

Why creating parameters for each field that will be updated helps avoid SQL Injection?

Because parameters are evaluated before they are inserted. In this example with AddWithValue() the check is not very efficient because there is no check of the parameter data type. Here talks more about.

For these cases, prefer the method Add():

comm.Parameters.Add("@Telefone", SqlDbType.Int);

Answer 2

These methods know how to clean the content, they remove any potentially dangerous text. As there is expected something very limited, ie just numbers or a text like string (in quotes), anything that escapes from this is considered garbage and is discarded. So elements that are part of the SQL syntax cannot be injected into the query by changing its content.

The injection problem occurs by constructing the query text freely, accepting anything as something valid. Protection occurs precisely because it allows only some very specific parts to be used flexibly and come from an external source.

The problem can occur in any normal programming language and the error is always of the programmer, because he doesn’t know how to avoid the problem manually or because he doesn’t use the ready-made tools that the library or language offers to insert the parameterized data and only them, without letting other data especially enter some that can be confused with a valid SQL that can be executed.

Here comes the famous XKCD

It is very common for the programmer to concatenate strings to create the query that will be sent to the database naively, then it does something like this:

query = "SELECT * FROM Students WHERE (nome = '" + GetByName + "' AND status = 'ativo')";

Where this variable nome is a text that came from an external source, for example came from a POST or GET HTTP on a website a web application. None of this has been sanitized and concatenation occurs like this. Something like this is valid:

http://site.com/Student?GetByName=Joao

So too:

http://site.com/Student?GetByName=Robert');%20DROP%20TABLE%20STUDENTS;%20--

There the malicious subject or with a name a little out of the ordinary can cause damage to your database. Assuming the example of this comic string would be Robert'); DROP TABLE STUDENTS; --, concatenated like this:

SELECT * FROM Students WHERE (nome = 'Robert'); DROP TABLE STUDENTS; -- ' AND status = 'ativo')

This is a perfectly valid command that will:

• select all table columns Students applying a filter where the column nome have the text Robert
• soon after closes this SELECT with the ; and starts another command where the table Students is erased completely
• Finally there is a comment that can have any text that will not give error, it is all ignored by the database.

If you want an easier read:

SELECT * FROM Students WHERE (nome = 'Robert');
DROP TABLE STUDENTS;
-- ' AND status = 'ativo')

Note that I used an agnostic syntax of language in string up there, almost everyone would have the command like this (there is no SQL injection without a code in a programming language at some level).

Obviously any SQL code can be injected and not only to erase a table that is a little useful. There are millions of systems out there (mostly web) that are being misused causing problems and small losses without the person noticing for years. In general who does this takes care not to arouse too much attention to do for too long and get greater benefits. In sum the losses are enormous. There are cases where you don’t even need to change anything, just having access to information without the right can cause a lot of damage. Example:

"SELECT * FROM Correntistas WHERE numConta = '" + numConta + "';"

That’s how it’s done:

http://site.com/Correntistas?numConta=1%20OR%20TRUE

I put in the Github for future reference.

The example is naive but shows that you can do anything, just understand how is the query on the other side and almost all vary very little, all hackerLamer knows how to do it with your foot in the back. It’s one of the easiest things to violate that exists and the most neglected.

Because it can take a lot of work and you don’t see all the details that are necessary to ensure that you don’t have the injection, it’s best to use a ready-made function of the language library or the database access component. Each will have their own way of doing this and the example used in the question is the correct way of doing it in C#.

Another source of poor Bobby Tables' joke is his website showing the problem and solution in various languages.

That’s why I’ve seen people with names Null have problems, nor was it Injection purposeful.

It is more common than it seems. It occurs more among amateurs but turns and moves some great software made by experts falls into this. Note that today the solution is simple, one only needs to remember to use. The ones that may seem more elaborate, the manuals tend to fail, use the basics, the ready, as long as you can trust the tool you use (some well-known are not reliable). So understand everything on the subject to be safer.

Here’s more about:

• How to prevent SQL code injection into my PHP code?
• I am suffering attacks of type SQL Injection
• What is the question mark in a query?
• Explain in a simpler way what these attacks are?

Answer 3

How an SQL Injection Happens?

The malicious user puts SQL codes in the value of the variable and these codes are executed by the database.

Example:

Given the code:

SqlCommand comm = new SqlCommand("UPDATE Contatos Set Telefone = '" + telefone + "'" + 
                                 " WHERE Id = " + Id, conn);

If the user puts the value 9999-9999 the query will be:

UPDATE Contatos Set Telefone = '9999-9999' WHERE Id = 1

Nice, but what if instead of the phone number he put that line down?

' where 1=0; delete from Contatos; --

The result of the query will be:

UPDATE Contatos Set Telefone = '' where 1=0; delete from Contatos; --' WHERE Id = 1

Thus, an update would be executed that would not update anything, followed by a delete inserted by the user, and the comment to ignore the rest of the command.

Why creating parameters for each field that will be updated helps avoid SQL Injection?

As answered in this question: https://stackoverflow.com/questions/4892166/how-does-sqlparameter-prevent-sql-injection

Because when you run a Sqlcommand using Sqlparameters, the parameters are not inserted directly into the command. Instead, a stored native Procedure called sp_executesql is called and takes the command string and an array of parameters. With this the parameters are isolated and treated as data instead of being embedded in the query, hence no command contained in the parameters can be "executed".