MySQL / PDO :: quote (), Putting single quotes around integers

Question

It seems that no matter which data / data type pair is passed in $pdo->quote($value, $type); , she always quotes it as a string:

 echo $pdo->quote('foo', PDO::PARAM_STR); /* 'foo', as expected */ echo $pdo->quote(42, PDO::PARAM_INT); /* '42', expected 42 unquoted */ 

I'm just curious to know if this is an assigned functionality. I use prepared instructions to actually execute the request, but I am trying to fetch the final requests (for debugging / caching) and create them manually.

As the name suggests, this is when $pdo is created using the MySQL driver. I have not tried others due to inaccessibility.

Answer 1

Oracle , SQLite , MSSQL, and Firebird drivers are cited as the MySQL PDO driver , ignoring the parameter type. PostgreSQL driver distinguishes between binary large objects and all the rest; ODBC driver does not implement quotation. The expected (absence) behavior was reported as a bug and closed as "dummy", which means that the behavior is by design. Perhaps the documentation is misleading when it states:

PDO :: quote () puts quotes in the input string (if required)

Although this suggests that there may be times when the values ​​are not surrounded by quotation marks, he does not say what definitely is and does not indicate what these instances are. If you think this is a mistake in the documentation, send a bug report preferably with a correction.

Answer 2

  public static function quote($value, $pdotype = PDO::PARAM_STR) { if ($pdotype == PDO::PARAM_INT) return (int)$value; return Db::pdo()->quote($value, $pdotype); } 

According to PDO developers, this is a deliberate mistake in their code and in their documentation.
It seems that they do not plan to fix it, so you can do it yourself by wrapping your erroneous function and replacing it as necessary.
You really have no choice, since in some cases you need to have the correct quoting behavior for numbers, you cannot just use string quoting everywhere, because SQL may just not accept it.

As a side element, the above function will make 0 of any illegal data.
SQL injections are not possible, but this will not result in an error. If you want to catch errors, you can make "strlen" for both variables, and if this is different from what you know, a problem or attempted intrusion has occurred.