Databases are awesome except for SQL injection attacks
by Jeremy Conley
Posted in Business, Coders, PHP on
The web as we know it today simply could not exist without databases. Massive, content-driven websites rely heavily on databases to store and retrieve large amounts of information. Databases provide websites with a way to efficiently store data. Instead of having to store thousands of individual HTML files, a single script connected to a database can serve an unlimited number of pages. In addition to storage space efficiencies, database driven sites make updates and user generated content extremely easy. Creating, updating, or deleting files on the file system is a cumbersome task at best, but moving all the content into a database makes those tasks much, much easier. All in all, databases offer much to websites in terms of increased functionality, scalability, and maintainability.
However, databases, just like every other piece of software, are vulnerable to attacks if developers are not vigilant. Databases themselves are usually not vulnerable per se since most Database Management Systems are built either by large corporations or open source developments and thus have many security reviews. But databases are vulnerable because they rely on correct inputs and there is little to no safeguards built in since they assume that the developer will send correct input. While this sounds terrible from a security standpoint, actually, it is a good thing since it forces us developers to be aware of security issues and not just blindly rely on third-party software to handle security checks for us. Awareness is the first step in developing a more secure application.
But how do SQL Injections work and how can they be prevented? An SQL Injection is simply adding additional commands to an otherwise legitimate query. One of the most common SQL Injections is an authentication check bypass. For example, a typical query to check for a valid username and password on login may be like the following:
SELECT * FROM users WHERE username = ‘$username_from_form’ AND password = ‘$password_from_form’
If both the username and password match what the user entered in the login form, the application grants access to the user. While this sounds like a secure system, in fact it can be exploited with a SQL Injection.
For example, what if a user entered ‘ OR 1=1;-- as the username? Before dissecting exactly what this does, let us take a look at the new query:
SELECT * FROM users WHERE username = ‘’ OR 1=1;-- AND password = ‘$password_from_form’
Actually, that looks like an invalid query and should crash, right? No, it is perfectly valid and will let a user login with neither a valid username or password. With this one command, an attacker can login as any user including an admin account if she or he knows the username. Looking at this query we can see that the single quote (or apostrophe) the attacker entered completed the username part in the WHERE clause. Now, the DBMS will see the query as username = ‘’ OR 1=1 Of course, you have done your work in the registration form to ensure that you do not have any users without a username so this evaluates to false. But, we have the OR 1=1 part which of course evaluates to true. So now the DBMS sees this as false OR true which will be evaluated overall as true since OR allows for either or both parts to be true.
Now, we see that the hacker is no longer forced to enter a valid username. But the hacker still needs to enter a valid password at least, right? Unfortunately, the answer is no again to that question. The final part of the hacker’s submission ;-- will end the query prematurely. As we already know, the semicolon is the end of a statement marker in SQL. However, what is less known is that the two dashes is a comment character. So, the DBMS will not even see the rest of the query checking for a matching password.
Any hacker can implement this attack in two tries or less. She or he merely has to discover what quote style the developer is using (either double or single quotes) and change the query accordingly. This is a frighteningly common attack because it can give an attacker so much power by bypassing authentication checks. If that is not bad enough, SQL Injections are limited by just the attacker’s creativity and goals. If an application is vulnerable to SQL Injection attacks, then any valid query can probably be entered by the attacker. Going back to our previous example, an attacker could append her or his query and add an INSERT statement to give her or himself a backdoor account to the database like this:
INSERT INTO users VALUES (‘hackeracct’, ‘mypassword’, ‘admin-level’);
The possibilities of this attack really are almost unlimited. A hacker could also issue UPDATE queries to launch a privilege escalation attack by giving other users admin access. Or an attacker could create SQL triggers and automatically tamper with data on any retrieval or insertion. Or a hacker could UPDATE a content block and add malware or add code to steal user data. Or the attacker could issue the ultimate command: DROP DATABASE. Or if the server is running MSSQL Server, the attacker could run xp_cmdshell and run any command line script. Ouch. In short, SQL Injections basically allow a hacker to have shell access to the DBMS and issue any command desired.
Thankfully, these attacks can be prevented easily. The first safeguard a developer should implement is to escape (i.e. render safe) all input into the database. As we have already seen, quote marks (either single or double) are field delimiters, so escaping them by adding a backslash removes their special meaning. If our developer had done this with the login form, the DBMS would have seen this as the query:
SELECT * FROM users WHERE username = ‘’ OR 1=1;-- AND password =’ ‘$password_from_form’
which would now fail since no user would have a username like that.
In addition to quote marks, databases will have other characters that have special meaning. For example, MySQL also considers NULL characters, line feeds, carriage returns, and more as all having special meanings. A developer should carefully research the chosen database to see what it considers as special characters and be prepared to remove them from queries. However, it is always safer for a developer to use a database native escaping function when possible. If a developer is using PHP and MySQL, the mysql_real_escape_string() (or its cousing mysqli_real_escape_string()) function will render all of these special characters harmless. This is a better approach since it is less error-prone since the developer does not have to remember each special character.
Even if the developer properly escapes special characters, a database may still be open to SQL Injections! For example, let us consider a typical web based Content Management System (CMS). A CMS will take a parameter and then pass that parameter to the database to retrieve the request page. A request may look like page.php?id=1 which will pull the page content with the ID of 1. An attacker could change this request from 1 to 1; UPDATE pages SET content = ‘’; and erase all the content. Escaping quote marks does not help here since numeric fields are not required to be surrounded by quotes. A developer must cast the page ID variable to an integer. In PHP, the developer could use intval() function to force the variable into a number or 0 if it is not.
Another defensive tactic a developer can use is to restrict database privileges to the least amount needed. For example, a news management system would not normally need stories deleted and only rarely edited. So, a developer might consider giving the application a database account with SELECT only access. And it goes without saying there is absolutely no reason for a news system to have DROP or TRUNCATE command privileges. So even if a SQL Injection hole was found, the attacker could not do much with it.
If a developer is using PHP and MySQL, then she or he has an additional protection. The PHP and MySQL connection does not allow for multiple queries at once so an injection that closes a quote then adds an INSERT, UPDATE, or DELETE command will not run since it is two queries. While this helps slightly, basic SQL Injections like the ‘ OR 1=1;-- attack still runs perfectly. This is an added layer of protection -- not a substitute for properly escaping input.
In closing, we have seen what SQL Injection attacks are, how they work, and how to prevent them. Now, that we know better, maybe we will become better developers than this school’s developer as illustrated in this XKCD cartoon:
about the author
More about Jeremy Conley:
Jeremy is a student at Western Michigan University where he is dual majoring in Electronic Business Design and Film & Video Studies. When not programming or researching design and security topics, Jeremy enjoys movies and photography and drinking coffee in all the amazing local Kalamazoo coffee shops.
questions or comments?
If you have any questions or comments about this article, feel free to contact us!