Game Security - Preventing SQL Injection
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):
Code:
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:
Code:
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:
Code:
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:
At first glance, that seems like a silly little snippet but when it gets combined with your original SQL statement, it now does this:
Code:
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:
Code:
'; drop table Scores; --
Becomes:
Code:
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?
Code:
'; update Scores set Username = 'HAXXOR' where ScoreID in (Select top 1 ScoreId from Scores order by Score desc); --
This becomes (Formatted for readability):
Code:
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.
Code:
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.
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!
