-3

How to address second-order SQL injection without using PDO or mysqli binding in PHP application. I did many research but all point to PDO which I do not have. mysql_real_escape_string PHP escape is just for protection against the quotes. Right now we are having legacy PHP which using mysql_connect. We cannot upgrade our system to use PDO or MySQLi since it would require dependencies installation. To over come injection we using escape. But this does not gives protection against second order SQL injection.

$name = $_POST['name']; $user = "Select user from user where name = $name"

now use $id to fetch user location. Note the current code does not do join as this is only example.

$loc = "Select * from location where user = $user"

currently we are using escape for $name and $user to prevent injection. But the issue is if query from first one contain SQL query then we have issue. So we need a way to escape the query that way second query will be safe

user332951
  • 359
  • 1
  • 3
  • 18
  • 3
    `mysql_real_escape_string PHP escape is just for protection against the quotes.` who told you that nonsense? Also `mysql_` is deprecated for a long time so you seem to have very outdated setup there – Marcin Orlowski Sep 12 '20 at 03:44
  • 2
    there is no difference between the first order or n-th order sql injection. the protection is all the same – Your Common Sense Sep 12 '20 at 04:46
  • Escaping should be done for every **string** you are going to interpolate into your SQL. It doesn't matter whether it's 1st or 2nd order. There's only one SQL injection. However, using PHP 5 is very irresponsible. If you take your users seriously you should do everything in your power to upgrade as soon as possible. – Dharman Sep 12 '20 at 10:38
  • 1
    The comments above are wrong, and they apparently didn't read your code example. They are being lazy and just automatically closing practically any question tagged PHP/SQL Injection as a duplicate of https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php. That one is great and it answers most SQL Injection questions, but it does not cover your case. – Bill Karwin Sep 13 '20 at 16:11

1 Answers1

0

You are correct that you can't use mysql_real_escape_string() when interpolating a whole subquery into your location query.

$name = $_POST['name'];

$user = "Select user from user where name = $name"

$user_esc = mysql_real_escape_string($user); -- WRONG

$loc = "Select * from location where user = $user"

Advice to defend against SQL injection often comes down to "use query parameters" and that is correct in many cases, but in this case it doesn't work.

The escape-string function and bound query parameters are both used only for protecting individual values in SQL expressions. Neither of these are useful for other parts of a dynamic SQL query:

  • Identifiers like table names or column names
  • Lists of values (for example inside an IN(...) predicate)
  • SQL keywords
  • SQL expressions
  • Whole SQL subqueries, as in your case.

In your case, your subquery is a fixed string under the control of your app, except for the $name variable. If you escape that variable then it's safe from SQL injection.

$name = $_POST['name'];
$name_esc = mysql_real_escape_string($name);

$user_query = "Select user from user where name = '$name_esc'"

Then you can use that subquery as part of the second query, and there is no risk of SQL injection.

$loc_query = "Select * from location where user IN ($user_query)"

By the way I made two small changes:

  • Put the subquery inside parentheses
  • Use IN( ) instead of =. If the subquery can return more than one row in its result, it's not allowed to use =.

I would also recommend you learn how to use joins in SQL. This is an ordinary and recommended way to use SQL. Usually if the tables are indexed correctly, a query using join has better performance than the query using a subquery, even if both queries produce the same result. Here's an example:

$name = $_POST['name'];
$name_esc = mysql_real_escape_string($name);

$loc_query = "Select loc.* from user join loc using (user) 
    where user.name = '$name_esc'";

I'll probably get downvoted for giving you a solution that works with mysql_real_escape_string(), because that function is deprecated and it is removed from current versions of PHP.

I do recommend upgrading to the current version of PHP and I do recommend using PDO and query parameters. They're easier, safer, and have better performance.

$loc_query = "Select loc.* from user join loc using (user) 
    where user.name = ?";

$stmt = $pdo->prepare($loc_query);
$stmt->execute( [ $_POST['name'] ] );

No need to escape the post variable when you use query parameters.

You will also find PHP 7+ generally has much better performance than PHP 5.x.

It's not true that you can't upgrade. It would just take some work.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • The statement of yours "The escape-string function ... used only for protecting individual values in SQL expressions" is not true and misleading. Not "values" but *string literals*. – Your Common Sense Sep 13 '20 at 17:55
  • Or quoted date literals. Or quoted numeric literals (which are odd but allowed). I'm sure you know that I agree that using query parameters is a superior solution. But the OP may be in a situation where their boss doesn't have budget for the work it would take for the upgrade. – Bill Karwin Sep 13 '20 at 19:22
  • There is no such thing as a "date literal" in mysql. Neither there is a "quoted numeric literal". It's either a numeric literal or a string literal. [A string is a sequence of bytes or characters, enclosed within either single quote (') or double quote (") characters.](https://dev.mysql.com/doc/refman/8.0/en/string-literals.html), no matter what particular character inside could be. – Your Common Sense Sep 13 '20 at 19:26