Attacks Against the SQL Back-End
![[Shadow of Hand on Keyboard]](/images/sqlinject.png)
The SQL back-end of a web application is a common target for attacks. The attacker may want to try and grab part of the database, inject malicious code into the database to be served to your users, or just disrupt your service. DOMBlogger takes a proactive approach to SQL security.
Database Users
When you use a database, your web application has to authenticate itself to the database in order to request data, store data, etc.
A database user is created with authentication credentials that the web application uses to talk to the database.
Most web application installations instruct the web master to set up an all powerful user who can do whatever it wants on the database from the web application. The attractiveness of this is that it allows easy automated setup from the web application. Click a few buttons and all your tables are created and the web application is up and running.
Unfortunately it also means that if an attacker can trick your web application into making a crafted SQL query, the attacker can do anything they want with your database.
DOMBlogger uses three different SQL users for three different roles needed in running a Content Management System:
Database Administrator
The database administrator is the all powerful user who can create tables, drop tables, dump the database to backup, reload the database from backup, etc.
That kind of power is dangerous, so the DB user that has that power is restricted in where the user can connect from. In the pg_hba.conf configuration file, this user is only authorized to connect from the actual host the database is running on (which may or may not be a different host than the web server) and must authenticate using the IDENT method. This means authentication is provided by the operating system, and not by PostgreSQL.
Attempts to connect as this user from any other host will fail. The only way to connect is to log in to the physical host the database is running on and connect using the operating systems authentication method.
Default Web User
This is the user that by far makes the most database queries. When someone requests a web page from the server, and parts of the web page that require data stored in the database must be retrieved from the database. Session information for the user may also need to be stored in (or retrieved from) the database.
DOMBlogger is set up to have a user with just enough permissions to perform these actions. The user for example may select records from the content table but does not have authority to insert records into the content table. If a bug in the web application allows an attacker to trick the web application into making an arbitrary query, damage can still be done but the extent of damage that can be done is greatly reduced.
Application Admin
When you connect the web application and authentication yourself to manage the web application or add content, a third database user is used. This user can not create or drop tables, but it can insert and delete records in all the tables where it needs to.
The web application only connects as this user after you have logged in and authenticated yourself to the web application. It would take a pretty serious bug for a none authenticated attacker to be able to trick the web application into connecting as this user.
SQL Injection
Even with the above separation of tasks and restriction of what the different database users can do, security of the database is not complete.
For example, the "Default Web User" mentioned above has to have insert permission on the blog comments table where it could be used to insert malicious code, and it can potentially be tricked into harvesting e-mail addresses or other sensitive data from tables it has select permission on.
The most common method of attack against a web applications back-end is a method called SQL Injection and it is at the top of the list of 2010 leading web application vulnerabilities according to the Open Web Application Security Project.
In a nutshell, an SQL injection is where SQL code the web application does not intend to run is injected into the query string that the web application runs, usually as the result of un-sanitized HTTP POST or GET variables that are incorporated into the query.
All you have to do is search Google for "wordpress SQL injection" or "joomla SQL injection" etc. and you will find plenty of scary examples of scary vulnerabilities that have been found and exploited in those products and their add-ons.
The vast majority of those exploits are the result in bugs with validating input before a query to the database is executed.
While validating input certainly is crucial, the fact that these exploits work almost always mean the web application was not using parameter binding prepared statements to issue their database queries.
Prepared Statements
A prepared statement does exactly what it sounds like it does. It prepares an SQL statement. The statement is prepared before the query is actually issued, using placeholders. For example:
$q = 'SELECT women FROM bbok WHERE hair=? AND eyes=?'; $sql = $pdo->prepare($q); $sql->bindParam(1, $preferred->hair); $sql->bindParam(2, $preferred->eyes);
What happens in the above, the ? are place holders. The statement is prepared by the database, the variables are then bound to the place holders. Sneaky methods of inserting SQL commands into the variables that most SQL injection attacks use just simply will not work because the actual SQL statement has already been crafted. All the variables do is fill in the blanks of the the already crafted query.
It literally boggles my mind how many web applications currently being developed do not use prepared statements for the query. I do not know if it is ignorance on the part of the developer or if it is laziness.
I think one of the problems is that MySQL became a very popular database very early on due to its raw speed, and it did not support prepared statements until MySQL 4.1 was released, which was late in the game (around 2005). By that time, the LAMP explosion was well past its full swing, and prepared statements just were not part of programming tutorials on the web because MySQL did not support them. As such many web application developers without formal programming training never learned to use them.
Sanitize User Input
The use of prepared statements will mitigate the vast majority of SQL injection attacks. However, they are not a substitute for properly sanitizing user input. Rather, they are a safeguard against crafty crackers who find ways to slip their attack past your filter sanitizing methods.
Every piece of user input needs to be validated by the web application before it used in an SQL query. The last thing you want to have happen is a vulnerability in your SQL server itself that can be exploited by a carefully crafted malicious string being passed as a bound variable in a prepared statement.
Sanitizing user input is always the first line of defense, that is where the attack starts. Using prepared statements is a second line of defense when the first line of defense fails, as it sometimes does.
DOMBlogger is very aggressive at sanitizing user supplied input before passing it on to the database. If the query field expects an integer between 7 and 27, an input of 6 would be rejected before a query is attempted.