Securing SQL Inject queries in ASP Classic

When saving a form, we usually take the value and pass it directly to a part of an SQL query string and run it in the bank, right? Wrong!

Before taking this data to the bank, we must treat it so that the wrong data is not in the middle of data and really important.

Therefore, I will clarify how in a basic way you can protect your system and your database from a known SQL Inject tactic, which is, in short, breaking this string to run another SQL query code native to the database, retrieving information that used to be it should not be recovered.

SQL break

Each type of database has its peculiarity, in the case of this example, we will see ways to protect the forms for SQL Server databases, and later MySQL, which are the two databases that are most in focus at the moment (on the web ).

Before protecting the form, we need to know how the query works and how it is possible to run several parameters in a single request.

SELECT codigo, usuario, senha FROM usuarios WHERE usuario = 'rodrigo' and senha = 'pula123';

Note the code above, this is a simple query to the bank to check if there is a specific user or not, going through so to speak.

Now the same query, in red, the code that was entered by a user.

SELECT codigo, usuario, senha FROM usuarios WHERE usuario = 'rodrig' or 1 = 1 or 1 = 'o' and senha = 'pula123';

In this case, as 1 and 1 is true, all users that exist in the bank will return, and the user would be granted access without any major problems.

These problems seem simple, but even affect sites that "know" how to deal with this type of content.

This is a test script found in the w3schools tutorial that is not protected, and that we were able to successfully perform SQL Inject. Of course, this is an example page and has no destructive character, and we have already warned them about the incident.

The URL entered with the code to display everything is:

http://www.w3schools.com/asp/getcustomer.asp?q=NO' or '' = '

Escape character to avoid SQL Inject

Every single quote character, in SQL Server must be entered as two single quotation marks so that it is recognized as part of the field's content, that is:

SELECT codigo, usuario, senha FROM usuarios WHERE usuario = 'rodrig'' or 1 = 1 or 1 = ''o' and senha = 'pula123';

In this case, the database will know that it is looking for the field containing: "rodrigo 'or 1 = 1 or 1 =' o", not interpreting the code in the middle of it.

In ASP Classic, use the replace function in Request.Form or Request.Querystring as follows:

campo = Replace(Request.Form("campo"),"'","''")

Do this with all fields before moving on to the SQL query.

In MySQL, there is another variation which is the escape character used in javascript, the "\". 

In a field that will be prepared for a MySQL query, we could then use:

campo = Replace(Request.Form("campo"),"'","\'")

Do this with all fields before moving on to the SQL query. 

Tip: If you upload files to the server, file names can have single quotes in the name, and can break the SQL string in the same way, so also save your query by exchanging single quotes for double quotes.

Final considerations

Always use common sense the database tools you use, see which escape characters are specific to your database and protect your forms from unwanted attacks that can happen overnight and that can be very painful Of Head.

Obs .: In ASP.NET there are methods that take care of forms that automate this security, but the purpose of this tutorial is to explain the basics, because this is what many make mistakes.