queries with db_select don't respect integers

Question

When I check "SHOW PROCESSLIST" in phpMyAdmin I can see lots of queries where argument-integers are being inserted as strings (with single quotation marks).

I'm wondering if this is due to the way phpmyadmin reports the queries (if it puts every argument between quotation marks) of if it is db_select that puts every argument between quotation marks.

An example of my code where integers are being used as arguments:

$select->join('taxonomy_index', 't_'.$key, 't_'.$key.'.nid = n.nid AND t_'.$key.'.tid = :tid_' . $key, array(':tid_'.$key => (int)$tid));
$select->condition('t_'.$key.'.tid', (int)$tid);

As you can see I explicitly write (int)$tid but in my query log I can see the following:

... WHERE (t_0.tid = '10') ...

The big question is: How can I be sure that integers are being treated as integers in my queries? I don't want MySQL having to transform strings to integers.

Answer 1

Drupal doesn't alter the arguments you give it (apart from expanding arrays), but because they're passed directly to PDOStatement::execute, in which

All values are treated as PDO::PARAM_STR.

the quotes are inevitably added.

Assuming a recent-ish version of MySQL you can try disabling prepared statement emulation to let MySQL sort them out instead. In settings.php:

$databases['default']['default'] = array(
  'driver' => 'mysql',
  'database' => 'drupaldb',
  'username' => 'username',
  'password' => 'secret',
  'host' => 'dbserver1',
  'pdo' => array(PDO::ATTR_EMULATE_PREPARES => FALSE),
);

Answer 2

it's my understanding that Drupal's doing that when it sanitizes vars for database input.

I believe you can wrestle control over this if you use db_query() like so, but I've never tried this:

$vals = array('tid', 10);
$result = db_query("SELECT * FROM taxonomy_index WHERE tid = :type", $vals)->fetchCol();