-3

A simple question: I have a framework which does not use prepared statements (mySQLi). How can sql injections be avoided when I have some user input (made through a public form but no HTML-code is allowed). That comment has to be stored in a mysqli database.

What is the best practice to detect and remove possible injection code? Please no suggestions to use prepared statements, they are not availabe without rewriting the whole framework!

EDIT

I was NOT asking how to do a MySQLi query! I asked how to prevent sql-injections on the php-level. To repeat: some website visitor leave a comment in a plain text field. Inside this comment this visitor (the script kiddie) thinks, he can post a sql-injection.

Now again: how the delete this pseudocode from the comment BEFORE adding it to the database. Answers like "hey, use a prepared statement", "use pdo", "use this kind of query with these parameters" are NOT answering my question. Also answers like "change" your framework" or NOT adequate!

I know that this could such code could be removed by preg_replace(..) and so on. And yes, I know the opinion of some here not to use this technique .. so better do not answer in that direction!

Dharman
  • 30,962
  • 25
  • 85
  • 135
OSWorX
  • 136
  • 9
  • 3
    mySQLi supports prepared statements all right – Your Common Sense Oct 19 '19 at 07:56
  • 2
    What kind of framework do you use that prepared statements are not available. This is irresponsible. Ditch that framework immediately. – Dharman Oct 19 '19 at 20:53
  • All right, I make it that your site has been recently hacked and you are looking for a magic wand that would make it secure in a puff of smoke. I have bad news for you. There is no such magic wand. If it ever existed, nobody would really bother with prepared statements. SQL injection is not a certain list of codes that can be filtered out. SQL injection could be any code. – Your Common Sense Oct 20 '19 at 10:18
  • You can't remove SQL injection, especially not with `preg_replace`. The only known solution is prepared statements. All the other options are merely workarounds. You could escape like Bill said, but that is irresponsible and illogical. Do everything in your power to fix the code instead of introducing workarounds. – Dharman Oct 20 '19 at 10:18
  • @YourCommonSense no, not hacked and will never be hacked. – OSWorX Oct 22 '19 at 06:39

1 Answers1

2

You might think that using an "escaping" function like mysqli::real_escape_string() on every input is just as safe as using prepared statements.

Except:

  • Escaping doesn't work for numeric inputs, only quoted strings and quoted dates.

  • Escaping has some unexpected cases where it doesn't work. These are edge cases, but they exist. See some examples in answers for SQL injection that gets around mysql_real_escape_string()

  • Escaping is harder when writing code. Doing it correctly is time-consuming and meticulous, and easy to get wrong. Developers are often tempted to skip it.

Developers who care about preventing SQL injection recommend using query parameters instead for several reasons, including but not limited to the following:

  • Query parameters also work for numeric values.

  • Code that uses query parameters is easier to write and read, and less prone to mistakes. We expect that once they have the habit of using query parameters, developers use this method more reliably, and therefore vulnerabilities are less likely.

For example, you could write code like the following:

$sql = "INSERT INTO mytable (col1, col2, col3, col4, col5, col6) 
  VALUES ('" . mysqli_real_escape_string($_POST['col1']) . "', " 
  . $mysqli->real_escape_string($_POST['col2']) . "', '" 
  . $mysqli->real_escape_string($_POST['col3']) . "', '" 
  . $mysqli->real_escape_string($_POST['col4']) . ", '" 
  . $mysqli->real_escape_string($_POST['col5']) . "', '" 
  . $mysqli->real_escape_string($_POST['col6']) . "')";

Can you spot the mistakes? With enough time, I’m sure you can. But it will slow down your coding and may give you eyestrain as you look for missing quote characters and other mistakes.

But it’s so much easier to write this, and easier to read it afterwards:

$sql = "INSERT INTO mytable (col1, col2, col3, col4, col5, col6) 
  VALUES (?, ?, ?, ?, ?, ?)";

Query parameters are safe for more data types, and they help you write code more quickly, with fewer mistakes. That's a big win.

Like the other comments above, I disagree with your claim that MySQLi doesn't support query parameters. It's clearly shown in the documentation: https://www.php.net/manual/en/mysqli.prepare.php

If you have some wrapper code that uses MySQLi but doesn't support mysqli::prepare(), then I recommend you stop using that wrapper code. Either write features for your framework so that it uses query parameters, or else get another framework.

Also answers like "change" your framework" or NOT adequate!

Look, here's the truth: if you can't write code that handles user input safely, then you shouldn't accept user input.

You suppose there are ways of using preg_replace() but there aren't. There are so many ways of injecting code (either SQL or Javascript), that you would end up filtering out many legitimate user comments as well. How would your users react if your web app stripped quotes and apostrophes from their comments? What other characters do you need to strip out?

Query parameters are the most effective solution for this problem. Your resistance to them is absurd.

It's like an electrician who asks, "How can I prevent getting shocked? But I can't use insulated gloves or tools, so don't suggest that."

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828