-3

I was trying to look for mitigation of SQL Injection against my web application based on PHP and MySQL. The first rule is to sanitize the query; Hence I am using mysql_real_escape_string() function for that

Here is what my snippet looks like

if (is_string($string)) {
    return $mysqli->real_escape_string($string);
} else {
    return "";
}

Here, $string would contain the user-input. After this filtering and escaping, I would use INSERT INTO query to insert into database.

This filter, will thwart any malicious user inputs like haha' , inj'' etc as is_string() will detect those string and apply real_escape_string() to escape those evil characters. The only possibility I can think an attacker can do is use a Numeric payload for SQL Injection but I don't know any Numeric payload itself has caused Injection yet so far.

So, will this filter keep away the bad guys or is it bypassable ?

EDIT: I know Prepared statements are much better and a good coding practice while launching app in production. But for this question, I am specifically looking answer to how anyone can thwart this filter itself because it does seem strong to me!

user8877134
  • 87
  • 2
  • 9
  • 2
    Why can't you just use prepared statements? http://php.net/manual/en/mysqli.prepare.php – Terry Mar 11 '18 at 20:23
  • @Terry Thanks for the suggestion. I will for sure, But I am looking for response related to the filter I mention in question. – user8877134 Mar 11 '18 at 20:24
  • What's the difference between using `is_string` and not? Nothing... – AbraCadaver Mar 11 '18 at 20:27
  • @AbraCadaver `is_string` will see if the user-input is string , like `hello`, `hello'` , `hello"` etc. which attackers may use for Injection. So it returns true and apply escape_string function on it. sounds reasonable – user8877134 Mar 11 '18 at 20:29
  • Why not just escape it whether its a string or not? – AbraCadaver Mar 11 '18 at 20:31
  • @AbraCadaver because it will make a homegrown version of magic quotes? – Your Common Sense Mar 11 '18 at 20:32
  • The short answer is no. Please see [here](https://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-use?rq=1) for further information. – Ilias Mar 11 '18 at 20:34

2 Answers2

-1

NO: PHP delusion #1: Mysql(i)_real_escape_string prevents SQL injection

This filter, will thwart any malicious user inputs like haha' , inj''

Your ideas are anything but a protection. There is nothing "malicious" in user inputs like haha' , inj'', neither a really malicious input would contain any of these characters.

That said, any user input is a string, so you will create a mockery of the notorious magic quotes feature, much despised and long removed from the language.

Go for the prepared statements.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
-1

NO

is_string() will not protect against SQL injection, a numeric payload will not be able to cause any table damage or unwanted access regardless, and string sanitization does not protect against all SQL injection.

I should give you the spiel about why prepared statements are amazing and all that, but you yourself indicated that the point of the question was to point out flaws in sanitization

Why You Should Use Prepared Statements Over Sanitization

  1. There are situations where you want unsanitized data in your database, e.g. specially formatted text (like LaTeX, XML, or JSON), where you would need to de-sanitize data, which is not a 100% guarantee of accuracy (e.g. XML file which includes HTML entities like " would be changed to ", changing the data)

  2. Prepared statements can be re-bound and executed in very few lines

  3. Theoretically, if you have a query like the one below, sanitization will not save you(borrowed from here)

    $iId = mysql_real_escape_string("1 OR 1=1");
    $sSql = "SELECT * FROM table WHERE id = $iId";

Community
  • 1
  • 1
Ben
  • 2,200
  • 20
  • 30
  • first one doesn't make sense as escaping doesn't change the data. the second one is scarcely a benefit. only the last one is a real thing – Your Common Sense Mar 11 '18 at 20:53
  • @YourCommonSense Escaping plain text can change the meaning if it is in a format with significance beyond plain text display to the user. e.g. Sanitization can prevent JSON and XML data from being parsed correctly, and LaTeX can display improperly if sanitized. – Ben Mar 11 '18 at 21:26
  • I see you are talking of some HTML stuff. dunno where you get it, but real_escape_string **which is discussed here** will never do things like `"` – Your Common Sense Mar 12 '18 at 04:53