Learn what SQL Injection is, how it is achieved, and how to defeat it.
Episode #4-01 released on September 14, 2013
SQL Injection is a big problem with the internet, you can do tons of damage, and steal millions of accounts in this way. Today, learn what it is, how it is done, how it can be avoided, and how to know when you are dealing with amateurs.
What is SQL injection?
SQL commands are written in a very predictable fashion. This makes them vulnerable to manipulation from cross site scripting, and other forms of attacks. And, because the entire SQL query can be made entirely from variables from the GET, POST, HEADER, COOKIE and REQUEST super globals, this makes it entirely possible for anyone to compromise a portion or the entirety of databases that web-sites run from. Furthermore, even if you run SQL queries in read-only mode until you need to manipulate the database, it is possible to trick the query into giving the entire table, or database itself, revealing all the data available to the web-site.
How is SQL Injection achieved?
There are plenty of ways to achieve SQL injection attacks, but the most common requirement is knowing the table and row names. Like most other kinds of hacks, knowledge of the target is required. It is possible to guess, however, not everyone names things the same way.
Okay, besides knowledge of the database, how do we injection malicious code? We must engineer the variables to take advantage of the query in a way that benefits us, the hacker. If we know where in the query the variable is, we can modify it to using the variable to introduce new unintended functionality in the code, like emailing the database like below.
SELECT email, passwd, login_id, full_name
FROM members
WHERE email = 'x';
UPDATE members
SET email = 'steve@unixwiz.net'
WHERE email = 'bob@example.com';
The variable email was represented by:
x';
UPDATE members
SET email = 'steve@unixwiz.net'
WHERE email = 'bob@example.com';
There are three ways of preventing this kind of attack. Limit most queries to read-only modes. In MYSQLi, this means setting up two database variables, one with read-only functionality, the other having for read/write functionality, which should only be used for registration and updating of various data sets. In this case, a user with one email had his email changed, and now the malicious user can now attempt to use the lost password form. Two other ways of preventing this kind of attack include validating the field's contents, like applying a rule for email addresses, or using other types of alphanumeric rules. You can, also, add slashes, or escape the string.
Is using the form the only way to attack the web-site's database to get the data?
No. You may use the GET, POST, HEADER, COOKIE and REQUEST super globals to attack the web-site, meaning that you have to sanitize ALL inputs in your web-site, regardless of the type. If the variable can be changed, it must be sanitized. If you are trying to save sometime and want to blanket validate all variables, you can use a form of PHP like below.
foreach ($_GET as $key=>$value) {
$_GET[$key] = stripslashes($_GET[$key]);
$_GET[$key] = mysqli_real_escape_string($con, $_GET[$key]);
}
The super global GET can be exchanged for anything you need it for, to make sure you code is safe. This set of code is created for MYSQLi with a PHP5+ environment.
Now, I promised that I would explain how we are dealing with amateurs, at least when it comes to database creation, and code construction. It is relatively easy.
If you are having a web-site created for you, and doesn't ask the abilities of your server such as types of usable encryption, hashing, etc... then that is a clue.
If you have user accounts, and the program doesn't suggest using SSL to prevent account hijacking via sessions, then you have a problem.
And, more importantly, if the programmer believes that sensitive database variables don't need to be encrypted because you use SSL, fire him.
While encrypting everything may tax your server and cause a potential DDOSing accident from just having too many people logging in, you should iteratively hash all passwords, secret answers, etc.... You should use the strongest encryption available to you to protect all credit card information, addresses, etc...
Next week, I will explain what is, and how to iteratively hash correctly. I'll, also, explain how to prevent hash collisions in the process, how to prevent accidental DDOSing in the process.
Remember to like this episode if you were interested in today's topic, share if you think someone else could benefit from the topic, and subscribe if you want to learn more. For the show notes of this episode and others, for more information on other ways to subscribe to our show, to subscribe to our weekly newsletter, and how to participate by submitting your questions, comments, suggestions, and stories, head over to TQAWeekly.com.
Host : Steve Smith | Music : Jonny Lee Hart | Editor : Steve Smith | Producer : Zed Axis Productions