postgresql - Prevent SQL Injection In This PHP Code

Question

I have the following function that writes into a PostgreSQL database. I need to make it safe from SQL injection however I am not sure how to do that.

The part of the query assembled from pg_query_params is safe from injection (or so I have been told) however the other part of the assembled query via PHP's string concatenation . is apparently vulnerable to injection.

private function setItem($table, $id, $field, $itemId, $fieldValue){

    $_1 = $itemId;
    $_2 = $fieldValue;
    $_3 = $field;
    $_4 = $table;
    $_5 = $id;

    $parameters = array($_1, $_2);

    // If the ID already exists, then update the name!
    $sql = 'update ' . $_4 . ' set ' .$_3 . ' = $2 where ' . $_5 . ' = $1;';
    /*$result = */pg_query_params($this->database, $sql, $parameters);

    // Add ID and Name into table.
    $sql = 'insert into ' . $_4 . '(' . $_5 . ',' . $_3 . ') select $1, $2 where not exists(select 1 from ' . $_4 . ' where ' . $_5 . '=$1)';

    $result = pg_query_params($this->database, $sql, $parameters);

    return $result;
}

Edit:

How can I prevent SQL injection in PHP? doesn't seem to address my concern.

I am using PostgreSQL and trying to find something compatible with pg_query_params

Answer 1

You can ask the database to secure your table and column names, using quote_ident(), before you create the query you want to execute. You need something like this:

<?php
$table = 'table name'; // unsafe
$column = 'column name'; // unsafe
$result = pg_query_params($connection, 
  'SELECT quote_ident(CAST($1 AS text)), quote_ident(CAST($2 AS text));', 
  array($table, $column)
);

$table = pg_fetch_result($result, 0, 0); // safe
$column = pg_fetch_result($result, 0, 1); // safe

$sql = 'INSERT INTO '.$table.'('.$column.') VALUES($1);';

echo $sql;

$result = pg_query_params($connection, $sql, array('foo'));
?>