SQL Injection Protection

I thought I would get your opinion on an option I thought that would essentially eliminate the possibility of SQL injection 开发者_JAVA百科(I think).

Currently, I have my admin account which obviously gives me full command of the database (alter, drop, etc). I than have an account that PHP uses which only has access to SELECT, UPDATE, DELETE, INSERT. What about setting up a user for each of those actions, and then just referencing the connect ID in the mysql_query statement - now obviously this would put a lot more strain on the server, having to make 4 connections perpage essentially, but if it security is important, it seems to me that this would be a valid option, by limiting the commands to EXACT function that you want performed in that case. Any thoughts on the viability of this option?

UPDATE: As I failed to mention earlier, this would NOT be the sole barrier of preventing SQL injection, mysql_real_escape_string(), prepared statements, etc. But I was just thinking maybe if by some how, ALL of these fail, wouldn't this at least limit the damage they could do? (e.g. on a registration form, they would be unable to SELECT hashes, or DELETE entries).

---

This is not what SQL Injection is all about. Any time you use parameters that haven't been sanitized in your SQL query you leave your database open to SQL injection, which might not necessarily have the goal of destroying data. It could also be to steal data or gain unauthorized access.

Consider a very restricted account where all it could do is SELECT. You write a query for authentication:

$sql = "SELECT COUNT(*) AS count
          FROM users 
         WHERE user_id='{$_POST['user']}' AND pass='{$_POST['password'}'";

// check if returns a count of 1, if yes, log in

With normal input, you expect the query to look like:

SELECT COUNT(*) AS count
  FROM users 
 WHERE user_id = 'username' AND pass='********'

Which should return 1 as the count if both username and pass match. Now an attacker tries to log in as admin. Since you haven't sanitized your inputs, they send $_POST['user'] as: admin'; --. The whole query becomes:

SELECT COUNT(*) AS count
  FROM users 
 WHERE user_id = 'admin'; -- AND pass='********'

Everything after -- is a comment, so this ignores the other condition and returns 1 regardless. There you go, you've just granted a malicious user admin access. That is how some real attacks are carried out. You start with a low privileged account and through holes in security you try to gain access to more privileges.

---

Long story short, having an application-wide account with restricted privileges (eg: no DROP, ALTER, etc) is good. Never give anyone or any application more privileges than they need. But to prevent SQL injection, use prepared statements.

---

Having separate user with limited privileges is a common practice. But even if you will have such a user, it won't protect you from SQL injection attacks.

Consider the following example:

mysql_query('DELETE FROM posts WHERE user_id = '.$user_id
           .' AND post_id = '.$_GET['post_id']);

Malicious user can trash all your posts with ease by setting post_id to 1 OR 1=1:

http://www.example.com/delete_post.php?post_id=1+OR+1%3D1

To get a valid protection you should always escape strings to be used in a query:

$_GET['post_id'] = mysql_real_escape_string($_GET['post_id']);

Or better use prepared statements.

---

This has very little to do with SQL Injection. You are talking about user permissions. Setting user permissions would have zero effect on protecting you from SQL Injections if you don't sanitize your user input and don't use prepared statements with PDO.

Read more about SQL Injections: SQL Injections and PDO

---

SQL injection and escaping sound magical to many people, something like shield against some mysterious danger, but: don't be scared of it - it is nothing magical. It is just the way to enable special characters being processed by the query.

So, don't invent new magial shields and ways how to protect the magical injection danger! Instead, try to understand how escaping of the input works.

The best would be just to have a look what escaping really does. Say the input string is:

');DROP TABLE table;--

after escaping:

\');DROP TABLE table;--

in fact it escaped only the single slash. That's the only thing you need to assure - that when you insert the string in the query, the syntax will be OK!

insert into posts set title = '\');DROP TABLE table;--'

It's nothing magical like danger shield or something, it is just to ensure that the resultant query has the right syntax! (of course if it doesn't, it can be exploited)

The query parser then looks at the \' sequence and knows that it is still the variable, not ending of its value. It will remove the backslash and the following will be stored in the database:

');DROP TABLE table;--

which is exactly the same value as user entered. And which is exactly what you wanted to have in the database!!

So this means that the if you fetch that string from the database and want to use it in the query again, you need to escape it again to be sure that the resultant query has the right syntax.

But, in your example, very important thing to mention is the magic_quotes_gpc directive!

This feature escapes all the user input automatically (gpc - _GET, _POST and _COOKIE). This is an evil feature made for people not aware of sql injection. It is evil for two reasons. First reason is that then you have to distinguish the case of your first and second query - in the first you don't escape and in the second you do. What most people do is to either switch the "feature" off (I prefer this solution) or unescape the user input at first and then escape it again when needed. The unescape code could look like:

function stripslashes_deep($value)
{
        return is_array($value) ?
               array_map('stripslashes_deep', $value) :
               stripslashes($value);
}

if (get_magic_quotes_gpc()) {
        $_POST = stripslashes_deep($_POST);
        $_GET = stripslashes_deep($_GET);
        $_COOKIE = stripslashes_deep($_COOKIE);
}

The second reason why this is evil is because there is nothing like "universal quoting". When quoting, you always quote text for some particular output, like:

1. string value for mysql query
2. like expression for mysql query
3. html code
4. json
5. mysql regular expression
6. php regular expression

For each case, you need different quoting, because each usage is present within different syntax context. This also implies that the quoting shouldn't be made at the input into PHP, but at the particular output! Which is the reason why features like magic_quotes_gpc are broken (never forget to handle it, or better, assure it is switched off!!!).

So, what methods would one use for quoting in these particular cases? (Feel free to correct me, there might be more modern methods, but these are working for me)

1. mysql_real_escape_string($str)
2. mysql_real_escape_string(addcslashes($str, "%_"))
3. htmlspecialchars($str)
4. json_encode() - only for utf8! I use my function for iso-8859-2
5. mysql_real_escape_string(addcslashes($str, '^.[]$()|*+?{}')) - you cannot use preg_quote in this case because backslash would be escaped two times!
6. preg_quote()