{"id":1799,"date":"2013-10-17T16:51:35","date_gmt":"2013-10-17T16:51:35","guid":{"rendered":"http:\/\/www.sitekickr.com\/blog\/?p=1799"},"modified":"2013-10-17T23:09:50","modified_gmt":"2013-10-17T23:09:50","slug":"sql-inserts-safe","status":"publish","type":"post","link":"https:\/\/www.sitekickr.com\/blog\/sql-inserts-safe\/","title":{"rendered":"Fun with SQL Injection"},"content":{"rendered":"

In case you're not familiar with SQL injection, you'll find a crash course below. Then, we'll have some fun coming up with examples to demonstrate the various ways that your data could be open to an SQL injection attack.<\/p>\n

 <\/p>\n

The SQL Injection Attack<\/h3>\n

SQL injection is essentially the act of providing input to a website or application which contains SQL commands, with the intent of altering or obtain data from a database in a way not intended by the application.<\/p>\n

As an example, let's have a piece of code that uses a query string parameter to pull data from a database.<\/p>\n

http:\/\/www.mysite.com\/getWage.php?employee_code=A8CNDU37<\/kbd><\/p>\n

SELECT name, wage\r\nFROM employee\r\nWHERE employee_code = 'A8CNDU37';<\/code><\/pre>\n
The employee_code is random enough that we're not to worried about people guessing it, and hence are comfortable pulling a value from the query string.<\/p>\n
But, let's say we used this query string:<\/p>\n
http:\/\/www.mysite.com\/getWage.php?employee_code=A8CNDU37' OR 'dummy' = 'dummy<\/kbd><\/p>\n
SELECT name, wage\r\nFROM employee\r\nWHERE employee_code = 'A8CNDU37' OR 'dummy' = 'dummy';<\/code><\/pre>\n
This query string has injected a logical OR into the SQL query where makes the where condition always true. In this case, the attacker is easily able to obtain the name and wage of the first employee in the database.<\/div>\n
 <\/div>\n
Now that you're comfortable with the term and how hackers make use of it, let's get to the examples.<\/div>\n
 <\/div>\n
 <\/div>\n
 <\/div>\n
Spoofer’s Delight<\/h3>\n
\"userA good place to start is with server variables. I’m talking about PHP's $_SERVER['x'], ColdFusions's CGI.X, Python's REQUEST.get('x') <\/kbd>or any variable passed down from the web server for use in your script.<\/p>\n
We tend to place a bit more trust in the validity of the data sent from our web server and, in turn, might feel more comfortable inserting this data directly into our database.<\/p>\n
Don't get too cozy with server variables, as many of them depend on the information passed by the web browser. One such variable, the HTTP_USER_AGENT<\/kbd>.<\/p>\n
You might be tempted to run a query like this, using PHP as an example:<\/p>\n
$sql = sprintf("INSERT INTO contact_form (user_id, email, browser)\r\n                VALUES (%d, '%s', '%s')",\r\n                $_SESSION['user_id'],\r\n                mysql->real_escape_string($_POST['email'],\r\n                $_SERVER['HTTP_USER_AGENT'])<\/code><\/pre>\n
 <\/p>\n
But what happens if a funny guy decides to alter the user agent string sent by his browser (using any of a myriad tools available to do so)?<\/p>\n
HTTP_USER_AGENT = Mozilla '); DELETE FROM contact_form WHERE '1' NOT IN ('
\n\t<\/kbd>
\n\tYou can delete all your data yourself, or you can wait until a human or bot to spoof it’s user agent string and do it for you!<\/p>\n
 <\/p>\n
Man on the Inside<\/h3>\n
\"sql-injection-delayed\"You may also think that data coming out <\/strong>of your database is safe, and there's little need to check it for SQL injection. But, in a typical web scenario, who is putting data into <\/strong>your database? Users!<\/p>\n
If an even funnier guy decides to create a username on your website:<\/p>\n
jerryseinfeld2' OR 
\n\tusername IS NOT NULL OR 'x' = '<\/kbd><\/p>\n
Inside this user's account is a nice interface where it lists previous quotes and estimates provided for the account.<\/p>\n
 <\/p>\n
SELECT quote_id, material_list, total_estimate\r\nFROM quotes\r\nWHERE username = 'jerryseinfeld2' OR username IS NOT NULL OR 'x' = ''<\/code><\/pre>\n
It may be a pain in the neck for this guy to log in to your site next time, but he doesn’t care. He just found out not only what materials his competitor is requesting quotes on, but also that his competitor is getting a lower rate!<\/p>\n
 <\/div>\n
The Free Pass<\/h3>\n
\"SQLThe SQL Union statement struts around disguised as an innocent way to combine the results of two select statements. But take off that mask and you have what amounts to a free pass for any hacker to retrieve data from any table in your database!<\/p>\n
Here's how a real miserable hacker might attempt to pull protected data from a table completely unrelated to the task at hand.<\/p>\n
Let's build off of the example above, where the hacker supplies a username <\/em>which contains SQL statements:<\/p>\n
x193ajx832jx' UNION ALL SELECT social_security_number, '', '' FROM users WHERE username = 'competitor<\/kbd><\/p>\n
Again, using the same example as above, only this time we're going to attack the users table. We want to grab the social security number of any username <\/em>we choose.<\/p>\n
Because we provide a totally random value for username <\/em>in the first select statement, we're guaranteed not <\/strong>to get a record from it. This means that we'll get the first record from the second <\/strong>select statement, which is targeted at a specific username<\/em>.<\/p>\n
SELECT quote_id, material_list, total_estimate<\/span>\r\nFROM quotes<\/span>\r\nWHERE username = '<\/span>x193ajx832jx' \r\n\r\nUNION ALL \r\n\r\nSELECT social_security_number, '', '' FROM users WHERE username = 'competitor'<\/span><\/code><\/pre>\n
 <\/p>\n
 <\/p>\n
How to Prevent SQL Injection<\/h3>\n
Your Language Has You Covered<\/h4>\n
Thieves have been around as long as their has been something to steel, and SQL injection has been around as long as SQL. This means that just about any language has methods for protecting your queries against SQL injection.<\/p>\n