Game Security - Preventing SQL Injection

[webgeek]

I plan on creating a few of these posts to help game programmers out with the slippery slop of application security. Please message me if you have a specific topic you'd like covered.

The Problem

SQL Injection is the process by which someone sends a snippet of SQL code in place of a real value and it gets executed. Let's say you have a "Scores" table that looks like this (in column name - data type format):

ScoreId - int (pk), Score - int, Username - varchar, Date - datetime

And lets say you support the ability to load all the scores for a specific person. The SQL for that would look something like this:

select ScoreId, Score, Username, Date from Scores where Username = '#name#'

Assume that #name# is the data coming from the client, just posted in directly. So in practice, the query might look like:

select ScoreId, Score, Username, Date from Scores where Username = 'webgeek'

Now a clever hacker (or a script kiddie with the right tools) will attempt to inject malicious SQL as their name. Something like this:

' or '1' = '1

At first glance, that seems like a silly little snippet but when it gets combined with your original SQL statement, it now does this:

select ScoreId, Score, Username, Date from Scores where Username = '' or '1' = '1'

And presto, they just returned every score in the system. This might cause huge performance problems or not, but it's something you didn't want them to do.

Now for the bigger problems. Based on our existing table and query, the evil hacker can go farther. They could drop your table and thus delete all your high scores:

'; drop table Scores; --

Becomes:

select ScoreId, Score, Username, Date from Scores where Username = ''; drop table Scores; --'

The semi-colon tells the SQL engine that it's the end of a query. Most engines will let you run queries back to back like that. The -- is a comment. So they just commented out your closing ' mark.

And for the best one?

'; update Scores set Username = 'HAXXOR' where ScoreID in (Select top 1 ScoreId from Scores order by Score desc); --

This becomes (Formatted for readability):

select
 ScoreId,
 Score, 
 Username, 
 Date
from
 Scores
where
 Username = ''; 
 update
  Scores
 set
  Username = 'HAXXOR' 
 where
  ScoreId in 
   (
    Select top 1
     ScoreID
    from
     Scores
    order by 
     Score desc
   );
 --'

The "top 1" command says return only the first record. That's specific to SQL Server and Access but there are equivalent statements for all databases. That query will simply update the highest score in the system with a new username. The name could be a real user and you would never know that your board is being hacked.

SQL Injection attacks can be used like a sledgehammer or a scalpel and they are VERY difficult to track down. A good hacker can take the "email my password" form and update an admin email address with his own, run the form, get the password, and update the email back. Presto, he now has admin access and no one is the wiser.

I won't go into more details on what can be done with SQL injection attacks because this isn't a guide for hackers.

The Solution

Input Validation
This is crucial. Every form on every web application needs server-side validation. This goes for data submitted from Flash to the server as well. Just think of that as a form without an HTML front-end. Validation on the client (Flash or JavaScript) is excellent and should be done but even if you have the greatest client-side validation you MUST also have server-side validation. Remember, the user has the client in their hands. It CAN and WILL be hacked. The server is MUCH harder to hack.

If you know that "username" can't be more then 30 characters then validate the fact that it's <= 30 characters before you think of using it in a query. If it's too long, just throw it out. Many of the sophisticated SQL injection attacks depend upon large SQL statements. If you know "score" should be a number greater then 0 and no more then 5 digits, validate that it's a number and between > 0 and <= 99999. If you validate before you process then you can help limit the type of attack they can do right off.

Error Handling/Reporting
This is another crucial step. In many web application frameworks or languages you get pretty error messages to help developers debug properly. These are good things in development but terrible in production. You want to ALWAYS use generic error messages when something goes wrong. Something like "failed to save score" would be good. This will avoid giving the hacker any information they can use to interpret the success or failure of their attacks.

If your application is stable (doesn't break too much) then I suggest going one step farther. Every time an error occurs, have it log that to a file or (even better) send an email with all the pertinent error details. This will help you debug it AND give you a heads up when someone is attempting to crack the application. This practice is used by many development shops out there and always pays off in the end.

Quote Escaping
It is possible to escape quotes and characters in the incoming data to prevent attacks. I DON'T recommend this though as it's a very difficult process to do properly (very database specific). You can't simply make ' become ''. That will NOT prevent attacks as there are many ways to escape and allow quotes in a given database. Sadly, this is just not a good approach to even attempt. It will provide a false sense of security and not make the app any more difficult to hack.

Prepared Statements
Believe it or not, there is a silver bullet (mostly) for SQL Injection attacks. Simply use "Parameterized Statements" (often called "Prepared Statements" as well). A prepared statement is one where you create a query with placeholders for your parameters and you ask the database to simply bind your values to those placeholders. What makes it so nice is that you don't have to worry about the quotes or anything else at all. To use our example from above, here is the parameterized version.

select ScoreId, Score, Username, Date from Scores where Username = ?

When you bind the parameter, the database will automatically quote it as needed. This will escape any invalid characters. The injection examples above will all fail with prepared statements. Additionally, prepared statements are significantly faster for repeat queries as the database internally optimizes them. If you use a prepared statement over and over again you will see a huge performance increase over concatenated string SQL statements (this is assuming you have appropriate statement caching enabled in your connection pool or on the database). This isn't an article on databases so we won't go into detail here.

The language/database you use dictates how you interact with prepared statements. In general it's quite simple. Both Java and .NET have database-neutral techniques that you use so your code is the same for Oracle, DB2, SQL Server, MySQL, etc. PHP has that if you are using Pear DB but not if you are going against MySQL (or other databases) directly without Pear.

Java with any database
http://www.theserverside.com/tt/arti...ared-Statments

.NET with any database
http://msdn.microsoft.com/library/de...ercommands.asp

PHP w/Pear DB (database abstraction layer)
http://www.codepoets.co.uk/doc/php_p...b_applications

PHP w/MySQL
http://www.webdesignforums.net/archi...p/t-18762.html

In lieu of prepared statements, you could also use stored procedures. They are a type of parameterized query. I don't go into them here though as most game developers seem to steer clear of them.

Database Security
No matter how good you are, it's very likely you will make mistakes. The catch-all for these mistakes is to lock down access to the database. In the high score example, the user account accessing the database only needs the ability to run insert and select statements against the "scores" table. If you restrict the account properly then it would be impossible for them to delete the scores table or even update records in the table. The way to do this is to simple create a few new accounts for your application. NEVER use your admin account to the database as the username/password in your applications. This step will dramatically reduce the damage that an enterprising hacker could do even if they do find a vulnerable form.

-=-=-

Hopefully this monster of a post proves useful to you guys. Feel free to let me know if you have any questions. Thanks!

[T1ger]

I've always thought I knew somewhat how SQL-injection works, dealing with PHP and MySQL often, but this was a really clarifying read. Didnt quite understand the thing about prepared statements, but will check the links on that one at a later time.

But really great, thanks for taking the time to write this

[Fall_X]

Webgeek, could you explain why escaping is not a good option? When using MySQL, I always thought escaping quotes was the way to go. I change every ' to \' using addslashes(), and for numbers I usually multiply their value by 1 just to be sure (not in mysql off course, I do it in the php script). I really think this should be enough, unless someone can prove me wrong.

When it comes to using Access though, it's a different story. I'm not sure if this is typical for all versions of Access, but I know that at least some versions don't allow you to put quotes around numbers, making it impossible to properly escape input strings. So in that case, I usually use recordset.addnew for adding records, but for select it's a bit more tricky. Don't like access anyway, though. SQL Server is a bit better.

[webgeek]

I promise I don't make this stuff up. Here is a brief explanation by Chris Shiflett on why "addslashes()" isn't safe.

In GBK (chinese character encoding supported by many databases), 0xbf27 is not a valid multi-byte character, but 0xbf5c is. Interpreted as single-byte characters, 0xbf27 is 0xbf (¿) followed by 0x27 ('), and 0xbf5c is 0xbf (¿) followed by 0x5c (\).

How does this help? If I want to attempt an SQL injection attack against a MySQL database, having single quotes escaped with a backslash is a bummer. If you're using addslashes(), however, I'm in luck. All I need to do is inject something like 0xbf27, and addslashes() modifies this to become 0xbf5c27, a valid multi-byte character followed by a single quote. In other words, I can successfully inject a single quote despite your escaping. That's because 0xbf5c is considered to be a single character, not two. Oops, there goes the backslash.

The article discussing that is located here:
http://shiflett.org/archive/184

What you really need to use is:
mysql_real_escape_string()

The reason I suggest against this approach is simply that you can avoid all this hassle AND be database agnostic by simply using parameterized queries. On top of that, you will get a significant performance increase. Most people using PHP don't care about database portability but in other languages it's important and code is often considered sloppy if it isn't database agnostic.

Either way, server-side validation is still a must at all times. Many other vulnerablies are exposed if you don't use good server-side validation.

[Fall_X]

Well, there you go. Thanks for the info, and I'm a bit ashamed that I didn't know this, after so many years of making websites. But apparantly, it's not a problem in most cases :

Not to downplay the vulnerability, but it is important to note that to make the exploit possible a multibyte character set needs to be used in MySQL. This is not the default configuration setting nor a common situation for most MySQL servers. So, while this exploit clearly demonstrates the need for the use of proper escaping function or better yet prepared statements, it is not something that would affect vast majority of LAMP systems.

Still, I'll go through the most critical sites that I'm maintaining, and replace addslashes with mysql_real_escape_string to be sure.

And parameterized queries are apparantly pretty slow when using php+mysql, but I didn't check this myself.

Most people using PHP don't care about database portability but in other languages it's important and code is often considered sloppy if it isn't database agnostic.

True, some database abstraction is never a bad thing. However, I find that it can complicate matters a lot if you take it too far. I've gotten quite some headaches working on multi-tier .net websites.

[webgeek]

Sadly, .NET fully supports making your code database agnostic but then they provide APIs that break it. IMHO: it's a really poor design decision on their part.

One framework for Java developers to look into is Hibernate. It supports totally database agnostic code. I routinely developer code for Electrotank clients in Derby (a free embeded database) and then give it to them to run against SQL Server, MySQL, Oracle and what not.

There is a .NET Hibernate project out there but I've not used it.

On the PHP/MySQL Prepared Statement front, these articles say its faster?
http://dev.mysql.com/tech-resources/...tatements.html
and more importantly:
http://www.databasejournal.com/featu...le.php/3599166

They both claim it's faster for the same reasons I mentioned about (query plan creation up front as well as caching the plan for subsequent queries). I'm not a PHP guy though (quite the opposite, I'm downright anti-php ) so I don't speak from experience there.

Have fun!

[Fall_X]

[pointless offtopic rant]How can you be anti-PHP? It's faster than asp (not sure if it's faster than asp.net), and granted, not all the function names are consistant, but other than that I don't see anything wrong with it. In fact, it has one feature that I think asp really misses : dynamic includes. You can include based on a string, which off course can be based on variables, which I use all the time and I miss it when I have to do asp.

And I really don't like .net for web development. For application development, it's a godsend, but not for the web. Well, I suppose for large projects where you work on it with a lot of people it may have it's advantages, but still. It makes things a lot more complicated than they need to be. I'm all for object oriented design, and seperation of code and presentation, but not at the cost of having less control over the html output.

I had to work on a .net site recently, which someone else made. It was all very clean and programmed very well, but adding one field somewhere meant changing it in the database, stored procedures, data layer, business layer and presentation layer. In a simple PHP script, it's a lot less work.[/pointless offtopic rant]

[webgeek]

I never said I like ASP either I hate it too.

I had to work on a .net site recently, which someone else made. It was all very clean and programmed very well, but adding one field somewhere meant changing it in the database, stored procedures, data layer, business layer and presentation layer. In a simple PHP script, it's a lot less work.

I would say then that wasn't a very well designed application. It suffered from poor seperation of concern. With that said, I'm not at all sold on .NET web apps either. C# is nice, but ASP.NET apps are a wierd mix of clean and messy code.

My personal preference is Tapestry (a java web application framework from Apache). The reason I prefer it over all others is that it doesn't make you put messy little script blocks in the HTML. That's my same complaint with ASP, JSP, Cold Fusion, and finally, PHP.

The UI of a web page changes all the time. It needs to be flexible. When I intertwine code with HTML, it becomes a pain in the a** to maintain. Tapestry is so gentle, that you can easily view a tapestry page off your local hard drive in the browser and it looks perfect.

Additionally, I like Tapestry because it treats all page events (button clicks, links, etc) as true events. I don't worry about passing form fields around and session state and all that rot. I simply wire up an event listener to do the work I need.

[tonypa]

Believe it or not, there is a silver bullet (mostly) for SQL Injection attacks. Simply use "Parameterized Statements" (often called "Prepared Statements" as well).

Bah, I thought it would be good idea to learn about it and improve my highscore system, but then I read:

"In PHP, you have to use the mysqli-extension to gain access to these new and advanced MySQL features."

If my host hasnt installed mysqli-extension then I cant use it

[webgeek]

Or use PearDB

[VENGEANCE MX]

I'm afraid to use Databases now.