SQL Injection via input from escaped database entry possible?

Question

While developing a PHP Application I thought about this situation: A user submits some kind of text in a form, on the serverside the text is escaped and inserted into the database. The question is: Is a SQL Injection in the last Query (which uses the value from DB) possible?

Example (I don't think that I have to explain my database class):

$db->query("INSERT INTO accounts SET test='".$db->escape($_POST["sometext"])."'");

Ok, so far I know nothing can happen, in theory i could have done this with an prepared Statement too, makes no difference.

Sometimes later the value of the column test is needed for use in another script and the value needs to get inserted somewhere else.

So the column is selected, and later inserted.

$db->query("SELECT test FROM accounts WHERE ... LIMIT 1");
$row = $db->fetchRow();
$db->query("INSERT INTO secoundtable SET test='".$row["test"]."'");

So as you can see, the value of the database is used in the last Query. Do I have to escape here?

Yes this is as subject to injection as much as when the user originally posted it. There is an argument for always sanitizing variables even if it comes from what you believe to be a trusted source — Orangepill, May 15 '13 at 16:28
Escape **any** and **all** parameters for your query. There is no room for assumptions here. The cost of missing even a single injection bug can be enormous. — tadman, May 15 '13 at 16:59

score 2 · Answer 1 · answered May 15 '13 at 16:29

You should be in the habit of always using properly parameterized queries.

In the first case, it's possible that:

The escape method has an error that causes it to not escape properly in some places
The code could accidentally be updated to remove the escape call

In the second case, the test column could have an apostrophe. That's bad enough, but if it comes from user input that's even worse. You could have a full blown attack on your hands.

I don't think there's any reason to avoid prepared statements / parameterized queries at all. They're easier to read, they're safe(r), they're easily reusable, etc.

score 2 · Accepted Answer · edited May 23 '17 at 12:21

The answer to this question largely depends on what the function $db->escape does. My guess is that it simply sanitizes incoming variables so that they are "safe" for input? If so, you're still susceptible to SQL injection attacks in your second query, simply because you are trusting user data.

Test it out for yourself by running the script:

//First query shouldn't cause you any issues (I'm assuming your function is safe).
$test = "'";
$db->query("INSERT INTO accounts SET test='" . $db->escape($test) . "'");

//Second query will cause you issues because you're taking the single quote from your
//table column and inserting it directly into the last query.
$db->query("SELECT test FROM accounts WHERE ... LIMIT 1");
$row = $db->fetchRow();
$db->query("INSERT INTO secoundtable SET test='".$row["test"]."'");

By the way, if you're wanting to be completely safe against SQL injections, you should look into using Prepared Statements. This topic here is well worth the read.

SQL Injection via input from escaped database entry possible?

2 Answers2