3

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.

  • Did you find a solution for this ? I am facing the same problem. Could you please help me out – maj Jun 19 '15 at 5:49
  • 1
    Hi maj, As it turned out, in the end it wasn't a problem for me. The reason I was diving into this was because of an exceptional high load in the database and I was figuring out how I could relieve the database load. It turned out that the source of my database load wasn't in the conversion of integers to strings but something totally different (monstrous queries). So i never went after a solution for the above problem. You could try the solution that's offered by Clive, below. – Bram de Leeuw Jun 19 '15 at 16:08
3

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),
);
0

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();
  • I was afraid of that. Since my query is very dynamic with lots of options I was hoping not having to use db_query(). – Bram de Leeuw Mar 25 '15 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.