Prevent SQL injection using php ONLY

Question

I think I have a full working site with many calls to the MySQL server and some research on this site I saw that makes my queries in this form:

$query = sprintf("SELECT * FROM users WHERE user='%s' AND password='%s'", mysql_real_escape_string($user), mysql_real_escape_string($password)); 

I can solve the security problem, but, as I said, I have many calls to the MySQL server, and the best way (in my case) to solve this problem is directly related to vars im sending the request, but without using the MySQL function, because im out of request. Let me explain this, I have the following:

 mysql_query("SELECT * FROM `post` WHERE id=" . $_GET['edit']); 

I cannot make changes to this request because I have a lot of all this in all my code so that I can check for injections on var, $ _GET ['edit'].

How can I use pure PHP validation for SQL injection for variable queries? How:

 $_GET['edit']=freehack($_GET['edit']); 

Answer 1

Do not do it like this. Replacing the value of your $_GET parameters with "safe" versions will pollute your input, which may be needed elsewhere.

Delete data only when you need to use it at the database level. It will take you a little time to fix your needs and save you a ton of headache in the long run.

In any case, what you are doing is still not safe. See: PHP: is mysql_real_escape_string enough to clear user input?

You really should use prepared queries with PDO . In addition, you must validate your user input before using it in a query.

Answer 2

“I can’t make changes to this request because I have a lot of this in all my code,” this is the wrong attitude when it comes to security. You have a major design problem that opens up all kinds of security issues. Even if you do something like the filter method that you describe at the end, you cannot be sure that you will cover every case.

You really should use some kind of database access class to query the database instead of making random calls like this in the whole code. Thus, you can write the sanitation code once and you can be absolutely sure that it is called everywhere. Refactoring is worth the extra security.

Answer 3

I think you can wrap your request inside a PDO .

 $unsafe = "SELECT * FROM `post` WHERE id=" . $_GET['edit']; $DBH->quote($unsafe); // makes query safe. 

where $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);

Then you need to write some kind of scripts to replace. Then I really think that you should rewrite your code from scratch because it is really ugly. Proper module testing, PDO, CSRF protection, OOP, etc.

Answer 4

I would not go with a “magic” function and expect it to clear the variables. This will not solve the whole region, and it will still be vulnerable.

A good example of this is your second request. It is still vulnerable to SQL injection even if you mysql_real_escape_string variable $_GET['edit'] .

What you need to do is check all the data you received and check to see if it is the data you expect.

 if (SomeValidator::validateInt($_GET['edit'])) { // OK, we continue // } else { // Display error, but don't continue ! // } 

Sanitation is only there, make sure that the data will be displayed correctly and not cause problems. You should not rely on disinfection to verify your data.

If you want validation and processing to work correctly, you can use ESAPI for PHP .

Answer 5

I would recommend using the PDO interface, or MySQLi inteface as support using prepared queries. Using prepared queries is the only way to effectively and consistently protect yourself against SQL Injection attacks. I personally recommend PDO over mysqli as it provides an agnostic database interface for your database if you ever need to switch databases. Even if you never need to switch databases, it is best to learn only one interface if you need to work with another project using a different database.

Answer 6

• Avoid repeating the same request in different places of your application - as things grow, you have several versions for support, and they almost always go out of sync. The prepared queries sound good to me (I'm not a PHP guy), or if you need to go the other way, create a central reference library and save your queries there and then refer to them when you need them in your application.

• Do not store encoded data, it is too easy to forget which variables are encoded and which are not, plus you end up having problems as soon as you have to use your data for a specific purpose with a different encoding, encode as early as possible in the process, ideally when he is placed in the final situation when he needs coding so that a quick inspection can show that this is being done.

• If you need ... SQL Injection is a type safety issue. If you know that you are expecting an integer parameter, "1; users with table separators; -" invalid input, even if it does not contain any dangerous characters or escape sequences. Do not just check for line breaks, etc., make sure that when you want to get a specific type, you get this, and another input causes an error.