Can someone explain what are sql injections, and how to prevent it?

Question

Hello

Please carefully read my question before answering it.

I almost finished my form, but I heard about sql injections.
I did some research regarding sql injections. I am not sure how to prevent sql injections. I know there are different options to consider. Can you please explain in a simple manner the options I listed below?
I would like to know how I can use the below options.
In addition, can you please explain in a simple manner what are sql injections, and how to prevent it?


OPTION 1

Injection Prevention - mysql_real_escape_string()


Lucky for you, this problem has been known for a while and PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function mysql_real_escape_string.

What mysql_real_escape_string does is take a string that is going to be used in a MySQL query and return the same string with all SQL Injection attempts safely escaped. Basically, it will replace those troublesome quotes(') a user might enter with a MySQL-safe substitute, an escaped quote \'.

Lets try out this function on our two previous injection attacks and see how it works.

MySQL & PHP Code:

//NOTE: you must be connected to the database to use this function!
// connect to MySQL

$name_bad = "' OR 1'";

$name_bad = mysql_real_escape_string($name_bad);

$query_bad = "SELECT * FROM customers WHERE username = '$name_bad'";
echo "Escaped Bad Injection:
" . $query_bad . "
";


$name_evil = "'; DELETE FROM customers WHERE 1 or username = '";

$name_evil = mysql_real_escape_string($name_evil);

$query_evil = "SELECT * FROM customers WHERE username = '$name_evil'";
echo "Escaped Evil Injection:
" . $query_evil;


Display:

Escaped Bad Injection:
SELECT * FROM customers WHERE username = '\' OR 1\''
Escaped Evil Injection:
SELECT * FROM customers WHERE username = '\'; DELETE FROM customers WHERE 1 or username = \''


Notice that those evil quotes have been escaped with a backslash \, preventing the injection attack. Now all these queries will do is try to find a username that is just completely ridiculous:

Bad: \' OR 1\'
Evil: \'; DELETE FROM customers WHERE 1 or username = \'

And I don't think we have to worry about those silly usernames getting access to our MySQL database. So please do use the handy mysql_real_escape_string() function to help prevent SQL Injection attacks on your websites. You have no excuse not to use it after reading this lesson!


OPTION 2


Using PDO:


$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {
// do something with $row
}



OPTION 3

Using mysqli:


$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name);

$stmt->execute();

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// do something with $row
}

Answer 1

You can rule out OPTION 1 very easily, for several reasons:



1) You have to remember to use mysql_real_escape_string everyplace you splice some untrusted string into a SQL statement. If you forget to do it in just one place, you have a vulnerability.



2) SQL written by splicing together a bunch of strings is UGLY and hard to read. You end up with too many: ='".$foo."'" constructs. Too many single quotes, double quotes and dots to be readable.



3) Look at the manual page for mysql_real_escape_string (first link). What does it say on the very top? It says: "This extension is deprecated as of PHP 5.5.0, and will be removed in the future."

You don't want to be writing new code today using deprecated functions that are going to be removed. That code will stop working someday. And all the mysql_ functions come with the same warning attached, so you need to be doing OPTION 2 or 3 anyway.



As for using PDO or mysqli: there really isn't a clear choice there. See second link. Look at how they both work, and decide which one feels more natural to you and use it. They are both supported.



For a better understanding of what sql injection is, see third link. Wiki does a decent job explaining it.



Once you have SQL injection covered, you should make sure you aren't vulnerable to XSS attacks either. See last link.

Answer 2

If you build SQL based on user input, the user might be able to run whatever SQL they want. That's called SQL injection.



For example, if you have code similar to this:

sql = "Select * From Users Where UserName = '" + userID + "'"



Then a user could enter a username of

5'; DROP TABLE users;--



And then the SQL you run would look like this:

Select * From Users Where UserName = '5'; DROP TABLE users;--'



And suddenly your whole table is gone. Or maybe they steal everyone's passwords, or credit card information, or whatever you have in your database.



This can be prevented by the ways you presented in your question. You can escape the string so people can't just use the ' character to get out of the username. Or you can use some sort of prepared statement that avoids this problem.

Answer 3

SQL Injection, in its simplest form - is someone trying to interrupt your conversation with the database by saying something that the database will execute, thinking it was you.



This is done by trying to break non-escaped inputs into the database by tricking the Database into thinking that it has finished the current command and to run a new one.



For example, SQL is written like this:



'INSERT into table(column) VALUES (value)';



As you can see, the SQL command is surrounded by quotations.



So, if someone was to submit the value '); it could trick SQL into thinking that that command was now over, and it would be ready to accept a new command.



This new command would be an SQL Injection.



To stop SQL from being tricked by pretending to finish commands, you can use OPTION 1 to make the (value) safe by "escaping" the quotations.



Escaping just means telling SQL *NOT* to treat the next character as a command. In most programming languages, this is the backslash.



So what OPTION 1 will do (and the other options) is search the (value) for any trickster characters, like quotations, that may trick SQL into finishing the command, and put a backslash in front of it -- so that SQL knows NOT to treat it like a real quotation.



The other methods do similiar things however they are already defined functions to make life easier for you.



Hope this helped.

Answer 4

All 3 options are crap - passing form parameters to a sql command is piss poor programming, escaped or not.

The proper way of doing it is using stored procedures and avoiding any form of dynamic sql unless absolutely necessary and you can control all the parameters.

In addition to eliminating the risk of sql injection, using a stored proc will logically decouple the database structure from the code, allow for separate testing and provide the option to change the table structure completely in the future with no code changes.

Simple example - your code to get a state name by state code may only support US states today. Next month it may include the requirement to get the state code of all countries. And next year it may require to get it in the default language for the country. Tables change, but as long as your SP keeps a compatible list of params your code will still work.

Answer 5

If you are using PHP then: $theValue = $HTTP_GET_VARS["myFormVariable"]; $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue; get_magic_quotes_gpc() returns true/false if addslashes() has been turned on globally. If it hasn't then do it manually each time. addslashes Returns a string with backslashes before characters that need to be quoted in database queries etc. These characters are single quote ('), double quote ("), backslash (\) and NUL. This also seems to help with with text box injection.