MySQL/PDO::quote() putting quotes around integers despite using PDO::PARAM_INT parameter

Question

It appears no matter what value/data-type pair I pass to $pdo->quote($value, $type);, it 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 the intended functionality. I use prepared statements for actual query execution, but I'm trying to ~~fetch~~ create the final querystrings (for debugging/caching), and am constructing them manually.

As the title suggests, this is when $pdo is created using the MySQL driver. I haven't tried others due to unavailability.

What interpolated query string? Is this functionality you're adding (for e.g. debugging)? Prepared statements in MySQL don't interpolate values into the statement; prepared statement parameter values are notionally sent [out of band](http://en.wikipedia.org/wiki/Out-of-band) from prepared statements. — outis, Mar 18 '11 at 19:18

outis · Accepted Answer · 2022-08-16T23:17:05.027

6

The Oracle, SQLite, and Firebird drivers all quote as the PDO MySQL driver, ignoring the param type.
The MSSQL driver only checks the param type for whether it should use a national or regular character set (based on the PDO::PARAM_STR_NATL and PDO::PARAM_STR_CHAR flags); otherwise, it ignores the param type.
The PostgreSQL driver only distinguishes between binary large objects and all others.
The ODBC Driver doesn't implement a quoter.

The (lack of) behavior you expect was reported as a bug and closed as "bogus", meaning the behavior is by design. Perhaps the documentation is misleading when it states:

PDO::quote() places quotes around the input string (if required)

While this suggests there may be instances when values aren't surrounded by quotes, it doesn't say there definitely are, nor does it state what those instances are. If you feel this is a bug in documentation, submit a bug report, preferably with a fix.

edited Aug 16 '22 at 23:17

answered Mar 18 '11 at 19:16

outis

75,655
22
151
221

Alright, thanks **outis**; I just wanted to make sure, as since it is the intended functionality I shouldn't expect (m)any problems. I believe it was the PHP docs, your quoted line specifically, that left me wondering. – Dan Lugg Mar 18 '11 at 20:20
There is an open bug report on this issue. As the answer suggests, there is debate as to whether or not this is a code bug, a documentation bug, or neither. https://bugs.php.net/bug.php?id=44639 – gerard Oct 28 '12 at 20:48
@gerard: that looks like a different bug, as it's in `PDO::bindValue` rather than `PDO::quote`. Also, [type conversion in `PDO::bindValue`](http://git.php.net/?p=php-src.git;a=blob;f=ext/pdo/pdo_stmt.c;h=31932eb3c2a728439388cc004967d046dff6917c;hb=HEAD#l1647) looks to be handled separately than in `PDO::quote`, as the former makes use of [`really_register_bound_param`](http://git.php.net/?p=php-src.git;a=blob;f=ext/pdo/pdo_stmt.c;h=31932eb3c2a728439388cc004967d046dff6917c;hb=HEAD#l302). – outis Nov 03 '12 at 05:58
I can't imagine what sort of developer would close such a bug as "bogus". I guess that's the "opensource" problem, developers often get unprofessional and have a "godlike" feeling even if they act bad no superior can blame them. It's clearly a bug no matter if others also do it wrong. I've had cases where mysql did not accept quoted integers (like in the LIMIT area). – John Oct 17 '16 at 19:32
Also, you can disable emulation mode and let MySQL sort out the placeholders by itself, though this may not work with all DB drivers (though will with MySQL) by: `$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, false );` – Carlos Roldán Aug 16 '22 at 19:00
@JuanC.Roldán: whether prepares are emulated or not doesn't affect the behavior in question, which is specifically for `PDO::quote`. As with gerard's comment, the behavior of other PDO methods & the C functions that implement them (such as those handling parameter binding) aren't relevant here. – outis Aug 16 '22 at 23:20
You are right, it does not affect to `quote`. It did the work for me on `prepare` though – Carlos Roldán Aug 18 '22 at 21:42

score 0 · Answer 2 · answered Oct 17 '16 at 19:34

  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 the PDO developers it's a intentional error in their code and in their documentation.
They do not seem to plan to correct it, so you can do it yourself by wrapping their errornous function and replacing the behaviour as needed.
You actually have no choice as in some cases you NEED a correct quote behaviour for numbers, you can't just use string quoting everywhere as SQL might just not take it.

As a sidenote, the above function will make a 0 out of any illegal data.
SQL injections are not possible but it will not throw an error. If you want to catch errors you could do a "strlen" on both variables and if that differs you know there was a problem or an intrusion attempt.

MySQL/PDO::quote() putting quotes around integers despite using PDO::PARAM_INT parameter

2 Answers2

Linked