is db_insert secure?

Question

I'm using the Drupal 7 method db_insert, to insert data into a custom table in the Drupal database. I've read that this is the prefered way, however I've walked through the code and doco and I can't see anywhere which parses the values, or tells me that these values are safe.

Some of the values are coming from the user so I need to check against SQL Injection attacks.

This is the example I was reading, where the Drupal 6 parses the values, and the drupal 7 version does not.

<?php
// Drupal 6 version
db_query('INSERT INTO {vchess_games} 
   (gid, timestamps, white, black, state, board_white, board_black) ' . "VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')", 
   $gid, $timestamps, $game['white'], $game['black'], $state, $board_white, $board_black);

// Drupal 7 version
db_insert('vchess_games')
  ->fields(array(
    'gid' => $gid,
    'timestamps' => $timestamps,
    'white' => $game['white'],
    'black' => $game['black'],
    'state' => $state,
    'board_white' => $board_white,
    'board_black' => $board_black
  ))
  ->execute();
?>

Answer 1

The Drupal database layer wraps around PDO and uses prepared statements, so yes, the insert statements are sanitised and protected from SQL injection attacks.

This quote from the Prepared Statements docs says it best:

The parameters to prepared statements don't need to be quoted; the driver automatically handles this. If an application exclusively uses prepared statements, the developer can be sure that no SQL injection will occur (however, if other portions of the query are being built up with unescaped input, SQL injection is still possible).

The same is true for all of the database functions in Drupal 7 (db_select, db_delete, etc.). The only one that is still potentially insecure is db_query() which will execute any arbitrary string you pass to it. Even with db_query(), though, you can pass in parameters so your query is safe.

The Database abstraction layer docs have some more information.