SQL Injection Protection

Question

I thought I would get your opinion on the option, which, as I thought, substantially excludes the possibility of SQL injection (I think).

I currently have an administrator account, which obviously gives me the full database command (alter, drop, etc.). I have an account used by PHP that has access only to SELECT, UPDATE, DELETE, INSERT. How to configure the user for each of these actions, and then just refer to the connection identifier in the mysql_query statement - now, obviously, this would put a lot more load on the server, since it is necessary to make 4 connections on the page in essence, but if this security is important, I it seems that this would be the right option, limiting the EXACT function commands that you want to execute in this case. Any thoughts on the viability of this option?

UPDATE: As I did not mention earlier, this would not be the only obstacle to preventing SQL injection, mysql_real_escape_string (), prepared statements, etc. But I was just thinking, maybe, if somehow, ALL of them fail, does that at least limit the damage that they can do? (for example, in the registration form, they will not be able to select SELECT or DELETE hashes).

Answer 1

This is not what SQL Injection means. Each time you use parameters that were not sanitized in your SQL query, you leave your database open for SQL injection, which may not have the goal of data destruction. There may also be data theft or gaining unauthorized access.

Consider a very limited account, where all she can do is SELECT . You write a request 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 request to look like this:

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

Which should return 1 as a counter if both the username and password match. Now the attacker is trying to log in as an administrator. Since you did not deactivate your logins, they send $_POST['user'] as: admin'; -- admin'; -- . The whole query will look like this:

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

Everything after -- is a comment, so it ignores another condition and returns 1 independently. There you go, you just got access to a malicious user. This is how real attacks happen. You start with a low privileged account and through security holes you are trying to access more privileges.

---

In short, having an application account with limited privileges (for example: 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 an individual user with limited rights is a common practice. But even if you have such a user, he will not 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']); 

A malicious user can easily delete all your messages by setting post_id to 1 OR 1=1 :

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

To get the right protection, you should always avoid the lines that will be used in the request:

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

Or it’s better to use prepared statements.

Answer 3

This has very little to do with SQL Injection. You are talking about user permissions. Setting user rights will have zero effect to protect you from SQL Injections if you do not sanitize your user input and do not use prepared instructions with PDO.

More on SQL Injection: SQL Injection and PDO

Answer 4

SQL injection and sound acceleration are magical for many people, a kind of protection against some mysterious danger, but: do not be afraid of it - this is not magic. This is just a way to include special characters processed by the request.

So, do not invent new magic shields and ways to protect the magical danger of injections! . Instead, try to understand how input escaping works.

It would be best to just see what really happens. Let's say the input line is:

 ');DROP TABLE table;-- 

after release:

 \');DROP TABLE table;-- 

in fact, he escaped a single slash. This is the only thing you need to make sure that when you insert a line into the query, the syntax will be OK!

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

This is not magic, like a firewall or something like that, just make sure the resulting query has the correct syntax! (of course, if it is not, it can be used)

The query analyzer then looks at the sequence and knows that it is still a variable, not its value. It will remove the backslash and the following data will be stored in the database:

 ');DROP TABLE table;-- 

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

So this means that if you select this row from the database and want to use it again in the query, you need to avoid it again to make sure that the resulting query has the correct syntax .

But in your example it is very important to note the magic_quotes_gpc directive!

This function automatically skips all user input (gpc - _GET, _POST and _COOKIE). This is an evil feature made for people not familiar with SQL injection. This is evil for two reasons. . The first reason is that then you need to distinguish between the case of the first and second requests - in the first case you do not run away, and in the second you do. What most people do is either turn off the "function" (I prefer this solution), or turn off user input first, and then, if necessary, escape from it. The unescape code might look like this:

 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 this evil is because there is nothing to do with "universal quoting . " When quoting, you always quote text for a specific output file , for example:

• string value for mysql query
• like expression for mysql query
• html code
• Json
• mysql regex
• php regex

For each case, you need a different quote, because each use is present in a different syntax context. This also implies that quoting should not be done at the input to PHP, but at a specific output ! For this reason, functions like magic_quotes_gpc are broken ( never forget to handle it, or better, make sure it is turned off !!! ).

So, what methods can be used for citation in these specific cases? (Feel free to correct me, there may be more modern methods, but they work for me)

• mysql_real_escape_string($str)
• mysql_real_escape_string(addcslashes($str, "%_"))
• htmlspecialchars($str)
• json_encode() - only for utf8! I use my function for iso-8859-2
• mysql_real_escape_string(addcslashes($str, '^.[]$()|*+?{}')) - you cannot use preg_quote in this case, because the backslash will be reset twice!
• preg_quote()