Properly Escaping with MySQLi | Query Over Prepared Statements

Properly Escaping with MySQLI | query over prepared statements

1. http://php.net/manual/en/mysqli-stmt.get-result.php
2. Yes, but it is very bad practice:
   
   • it will help you in this case but only in this case and deceive with anything else
   • manual escaping is just silly, better let driver to do it for you
3. YES, because there is no such thing like SQL injection but improper formatting ONLY

is that using $mysqli->real_escape_string($Var); does not provide protection against SQL Injection?

I didn't change my mind: sure, it doesn't.
It will do only if you enclose the resulting value in quotes (and set proper encoding using mysqli_set_charset() to be strict).

Look, SQL injection not something essential, existing on it's own, but it's rather mere a consequence. A consequence of improperly formatted query.

When creating a query, you have to properly format every part of it. Not because of whatever "injection" but for the sake of it. When you're going to insert a string into query, you HAVE to put it into quotes, or you will get a syntax error. When you're going to insert a string into query, you HAVE to escape these quotes were used to delimit this string, or you will get a syntax error. And so on. It is proper formatting that should be your concern, not scaring tales about injection. And as long as you have every dynamic query part properly formatted according to it's type - no injection ever could be possible

So, the source of variable or it's value should never be your concern. But only it's place in the query:

• strings have to be enclosed in quotes and have these quotes escaped.
• numbers have to be cast to it's type.
• identifiers have to be enclosed in backticks and have these backticks doubled

When it's going for the static part of the query, hardcoded in the script, we don't use such strict standards - say, we're not enclosing every identifier in backticks.

But when it's going for the dynamical part of the query, applying formatting rules should be strict rule, as we cannot know variable content for sure.

By the way, there is another way to format your strings and numbers - prepared statements. It is not as convenient as it should be, but because it is using placeholders to represent your data in the query, it it recommended to use over silly manual formatting.

Should you use prepared statements for their escaping only?

The difference considered to be negligible.

Nevertheless, one have to distinguish native prepared statements from the general idea of a prepared statement.

The former is just a form of running queries supported by most of DBMS, explained here. Its usage can be questioned.

The latter is a general idea of substituting actual data with a placeholder, implying further processing of the substituted data. It is widely used in programming, a well-known printf() function is an example. And this latter approach have to be ALWAYS used to run a query against a database, no matter if it is backed by native prepared statements or not. Because:

• prepared statement makes proper formatting (or handling) inevitable.
• prepared statement does proper formatting (or handling) in the only proper place - right before query execution, not somewhere else, so, our safety won't rely on such unreliable sources like
  
  
  • some PHP 'magic' feature which rather spoils the data than make it safe.
  • good will of one (or several) programmers, who can decide to format (or not to format) our variable somewhere in the program flow. That's the point of great importance.
• prepared statement affects the very value that is going into query, but not the source variable, which remains intact and can be used in the further code (to be sent via email or shown on-screen).
• prepared statement can make application code dramatically shorter, doing all the formatting behind the scenes (*only if driver permits).

So, even if you consider not using native prepared statements (which is quite okay), you have to always create your queries using placeholders instead of the actual data. For this purpose you can use PDO, which works exactly as described above - by default it just emulate prepares, means regular SQL query being created out prepared query and data, and then run against database.

However, PDO lacks support for many important data types, such as identifier or an array - thus it makes you unable to always use placeholders and thus makes an injection quite possible. Luckily, safeMysql has placeholders for the every data type and allows you to run queries safely.

Prepared statements and Escaping

the only problem with you query is that you cannot pass the tableName as a paramater. Only values can be parameterized. So the other way is to concatenate the tableName along with your query.

"SELECT * FROM `" . $tableNameHere . "` WHERE `uid`=?"

Why Mysqli prepared statement does'nt escape wildcards (% and _) and Backslash ( \ )?

Prepared statements do escape the \ properly. Otherwise they wouldn’t create properly formatted string literals, which is the main purpose of prepared statements.

Besides that, % and _ are only special characters to the LIKE comparison but not to strings in general. So there is no need to escape them in general. If you need to escape them for a LIKE comparison, do it explicitly:

$what = addcslashes($what, '%_');

Besides that, LIKE does also support the use of an escape character other than \:

mysql> SELECT 'David_' LIKE 'David|_' ESCAPE '|';
        -> 1

real escape string and mysqli prepare statement conflict

You don't need to use real escape string if you are using prepared statement. If you are using prepared statement strings are escaped automatically.

One advice: always use prepared statement instead of trivial method which involves use of real_escape_string

mysqli prepared statements and mysqli_real_escape_string

If you correctly bind all your variables you can dramatically reduce the risk of SQL injection. It is still possible to get an SQL injection if you create SQL dynamically for example:

'SELECT * FROM ' . $tablename . ' WHERE id = ?'

But if you avoid things like this it is unlikely you will have problems.

Are dynamic mysql queries with sql escaping just as secure as prepared statements?

Definitely NO.

While question in the title is ambiguous and can be interpreted as "Are dynamic mysql queries with every it's part properly formatted..." and thus have a positive answer, the question in the body is not:

If I ran all data received from the user through mysql real escape would it be just as secure as using mysql prepared statements?

If you look to this question closer, you will understand that this is just a magic quotes incarnation! The very purpose of this disgraced, deprecated and removed feature is exactly to "run all user input through escape".

Everyone knows nowadays that magic quotes are bad. Why positive answer then?

Okay, it seems that it needs to be explained again, why bulk escaping is bad.

The root of the problem is a quite strong delusion, shared by almost every PHP user:

Everyone have a strange belief that escaping do something on "dangerous characters" (what are they?) making them "safe" (how?). Needless to say that it's but a complete rubbish.

The truth is:

• Escaping do not "sanitize" anything.
• Escaping has nothing to do with injections.
• Escaping has nothing to do with user input.

Escaping is merely a string formatting and nothing else.

When you need it - you need it despite of injection possibility.

When you don't need it - it won't help against injection even a little.

Speaking of difference with prepared statements, there is at least one issue (which already mentioned many times under sql-injection tag):

a code like this

$clean = mysql_real_escape_string($_POST['some_dangerous_variable']);
$query = "SELECT * FROM someTable WHERE somevalue = $clean";

will help you NOT against injection.

Beause escaping is just a string formatting facility, not injection preventer by any means.

Go figure.

However, escaping have something in common with prepared statements:

Them both doesn't guarantee you from injection if

• you are using it only against notorious "user input", not as a strict rule for the building ANY query, despite of data source.
• in case you need to insert not data but identifier or a keyword.

To be safe in these circumstances, see my answer explaining FULL sql injection protection how-to

Long story short: you can consider yourself safe only if you make 2 essential corrections and one addition to your initial statement:

If I ran all data received from the user through mysql real escape and always enclose it in quotes (and, as ircmaxell mentioned, mysqli_set_charset() is used to make mysqli_real_escape string() actually do it's work (in such a rare occasion of using some odd encoding like GBK)) would it be just as secure as using mysql prepared statements?

Following these rules - yes, it would be as secure as native prepared statements.

Why is using a mysql prepared statement more secure than using the common escape functions?

An important point that I think people here are missing is that with a database that supports parameterized queries, there is no 'escaping' to worry about. The database engine doesn't combine the bound variables into the SQL statement and then parse the whole thing; The bound variables are kept separate and never parsed as a generic SQL statement.

That's where the security and speed comes from. The database engine knows the placeholder contains data only, so it is never parsed as a full SQL statement. The speedup comes when you prepare a statement once and then execute it many times; the canonical example being inserting multiple records into the same table. In this case, the database engine needs to parse, optimize, etc. only once.

Now, one gotcha is with database abstraction libraries. They sometimes fake it by just inserting the bound variables into the SQL statement with the proper escaping. Still, that is better than doing it yourself.

Escaping % sign in mysqli prepare() with LIKE clause and NO_BACKSLASH_ESCAPES mode

Update 14-07-2018: (The Solution)

As @Progman's comment pointed to the mysql docs, here is the summary from the docs:

Note

Because MySQL uses C escape syntax in strings (for example, \n to
represent a newline character), you must double any \ that you use in
LIKE strings. For example, to search for \n, specify it as \\n. To
search for \, specify it as \\\\; this is because the backslashes are
stripped once by the parser and again when the pattern match is made,
leaving a single backslash to be matched against.

Exception: At the end of the pattern string, backslash can be
specified as \\. At the end of the string, backslash stands for itself
because there is nothing following to escape.

Please do note that the above summary is for cases when NO_BACKSLASH_ESCAPES is disabled.

That said, here is my simple understanding about this:

The string in LIKE clause passes through 2 things:

(1) The Parser

(2) The Pattern Matcher

Thus, when looking for a literal \ in normal mode, we have to double escape it as \\\\, once for the parser and once for the pattern matcher.

When looking for a literal \ in NO_BACKSLASH_ESCAPES mode, we can skip the escaping that is meant for the parser (because this mode makes the parser take them as literal), BUT we must escape once that is for the pattern matcher. So, in this mode, we must use \\ to look for a literal \.

The NO_BACKSLASH_ESCAPES mode DOES NOT affect the Pattern Matcher!

---

To conclude:

(1) for same results, the normal mode requires 2x slashes than NO_BACKSLASH_ESCAPES mode, regardless of its use in = or LIKE. These are same:

SET @@sql_mode = '';
SELECT * FROM table where name LIKE 'abc\\\\def';

and

SET @@sql_mode = 'NO_BACKSLASH_ESCAPES';
SELECT * FROM table where name LIKE 'abc\\def';

both returns abc\def as result.

(2) for same results, the LIKE requires 2x slashes than =, regardless of its use in normal or NO_BACKSLASH_ESCAPES mode. These are same:

SELECT * FROM table where name LIKE 'abc\\\\def';

and

SELECT * FROM table where name = 'abc\\def';

both returns abc\def in normal mode and abc\\def in NO_BACKSLASH_ESCAPES mode.

Thanks a ton, @Progman!

how to use mysqli_real_escape_string in PHP Prepared Statements?

Prepared queries (when used properly) will ensure data is properly escaped for safe querying so you dont need to use mysqli_real_escape_string at all. You are kind of using them properly, just need change one little thing. Because you are using the '?' placeholder, it is better to pass params through the execute method.

$sql->execute(array($test));

Just be careful if you're outputting that to your page, database sanitization does not mean it will be safe for display within HTML, so run htmlspecialchars() on it as well.
Here is a link to this question here for prepared statements and real_escape_string

---

Related Topics