What is SQL Injection?
SQL Injection, as noted in jacek's security video, is a way an attacker can gain unauthorized use of your database by manipulating your query through unfiltered data. I'm not going to give an example as that can be considered internet terrorism even if I say it is to educate. There are a few sites that have been granted approval by the government.
What are Prepared Statements?
Prepared Statements are an intermediate to advanced level way to query your database. Take this example from php.net (Procedural style just in case of confusion):
<?php
$link = mysqli_connect("localhost", "my_user", "my_password", "world");
/* check connection */
if (mysqli_connect_errno()) {
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
$city = "Amersfoort";
/* create a prepared statement */
$stmt = mysqli_stmt_init($link);
if (mysqli_stmt_prepare($stmt, 'SELECT District FROM City WHERE Name=?')) {
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "s", $city);
/* execute query */
mysqli_stmt_execute($stmt);
/* bind result variables */
mysqli_stmt_bind_result($stmt, $district);
/* fetch value */
mysqli_stmt_fetch($stmt);
printf("%s is in district %s\n", $city, $district);
/* close statement */
mysqli_stmt_close($stmt);
}
/* close connection */
mysqli_close($link);
?>
You may be scratching your head at this but it's ok.mysqli_stmt_init($your_db_connection) - Initializes the statement.
mysqli_stmt_prepare($your_query) - Sends the query to the server BEFORE the variables. Variables you normally would put in your WHERE clause you replace with a question mark, ?
mysqli_stmt_bind_param($your_initialized_stmt, "s,b,d, or i - Refer to description", $vars) - In the second parameter you will tell the statement which field contains what. So if you had a string "test" and an int of 5, you would enter "si". Full list of types below. For the third, fourth, and so on parameters until you run out, you will enter the variables that correspond to the question marks in your query. The number of letters in parameter 2, and the number of vars, along with the amount of question marks in your query MUST match.
s - Indicates string
d - Indicates double
b - Indicates boolean
i - Indicates int
The rest are pretty self explanatory. If you need descriptions, let me know and I will edit.
Now, after those descriptions, you may ask "Why do i care?" - Because prepared statements make SQL Injection through themselves impossible (vulnerability has yet to be discovered).
What happens in a prepared statement is that the query is sent in 2 parts. The first part sends the actual query to the database. The second part then sends the variables. So if a user were to enter (Insert SQL Injection here (not me)) then it would be in the database as (SQL Injection) but have no effect on the database. In very simple words, you can send parameters straight through the query from say $_POST['username'].
Why not to use only Prepared Statements
Unfortunately, yes, there are a few downsides to this method. The most notable and the only I will cover here being performance.
Because statements are sent in 2 parts, the server will be affected as though you ran 2 queries. That may not seem like much, but hopefully you're not running your site on one giant query
You may now be asking "Why not just manually escape then?" - Because although this is like running 2 queries, the queries are cached after running. So, if you call your statement again later in your script, such as a foreach loop, your query will generally use less memory than it would use foreach'ing normal queries.2nd Order SQL Injection
As far as I know, no one on this site has mentioned 2nd order SQL injection. Of course, search hasn't worked for months. So, with that being said, I will try to take this slow and provide good examples.
2nd order SQL Injection involves using data already inside your database in another query. This would not (hopefully) be a case that you would do, but say Bob was updating his profile. Bob didn't want to update his name so you just inserted his name that was already in the database like so:
<?php
require("connection.php");
// Note that these are normal queries because we don't want to affect performance
$result = mysqli_query($connection, "SELECT `stuff` FROM `profiles` WHERE `firstname` = 'Bob'");
$row = mysqli_fetch_array($result);
$edit_stuff = mysqli_query($connection, "UPDATE `profiles` SET `stuff` = '{$row['stuff']}");
echo "Woot!";
?>
Say the data under Bob's last name was added using a prepared statement and Bob entered (SQL injection here). When Bob changed his profile, the data in the database was not escaped, and now runs as the SQL Injection intention. The next morning you wake up to see your database deleted.Prepared Statements Conclusion
The last thing I have to say on the subject is to never trust data inside your database either. While you may have only insert permissions with one SQL connection, if a visitor found and exploited an SQL Injection vulnerability but could only insert, say an admin would generally have all permissions. When that admin went to do something and it tied in with that SQL Injection vulnerability, it would be run.
Why mysql_real_escape_string() does NOT 100% secure
While the majority of the internet lives under the assumption that their site is "uncrackable", SQL Injection is a complex thing. Here is an example where SQL Injection could occur.
<?php
require("connection.php");
// Say you're getting the info of the page via a get variable
// YOU assume get id only contains an int like 1
$id = mysqli_real_escape_string($connection, $_GET['id']);
// Note the non use of apostrophes around the var
$result = mysqli_query($connection, "SELECT `stuff` from `pages` WHERE `id` = $id");
?>
Now, this is going to be hard without using an example, but NOT all SQL Injection requires the use of an apostrophe. Since you didn't use apostrophes around your vars, a user can directly access your query without being escaped by mysql_real_escape_string();Integer based SQL Injection
This sort of ties in with jacek's example in his video ('user OR '1' = '1) but it does not require use of an apostrophe!
Here is the query we will be working with:
<?php
$query = mysqli_query($connection, "SELECT * FROM `table` WHERE `id` = {$id}");
?>
Once again, note the use of no apostrophes around the var. Now, say $id is retrieved from the URL or a form (anywhere a user can edit it) and is, as usual, filtered through mysql_real_escape_string();. Since this is completely integer based, and there are no apostrophes around your var, a user can manipulate the query so that instead of returning for that id alone, it returns everything. This is where it ties in with jacek's example. Except, you remove the apostrophes. The query now returns true and returns every row in the table no matter what id you give.Use the least privileges
So many websites these days have one mistake in common. That mistake is using one user connection for an entire website. If you're doing what most beginners do and are using a single database user with all privileges STOP. So many sites these days fall victim to SQL Injection and are completely defaced because of allowing DELETE, DROP, UPDATE, etc. where you only need SELECT. You should be using the bare minimum privileges. For example, a login should select the count of users where username and (hashed and salted) password are the same. That connection should use a database user with only SELECT command privileges. **Tips:
1. Any query using a WHERE clause will need SELECT command privileges.
2. Close connections after use of the needed privilege when using more than one connection in a script. It's true that connections auto close at the end of the script, but when using multiple connections in one script...well it's pretty self explanatory. Conserve resources
Hope you learned something.
There are so many more forms of SQL Injection and I will updating accordingly once I have time to.
