SQL Injection Protection

Question

I thought I would get your opinion on an option I thought that would essentially eliminate the possibility of SQL injection (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).

Answer 1

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.

Answer 2

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.