在此 PHP 代码中防止 SQL 注入 [英] Prevent SQL Injection In This PHP Code

问题描述

我有以下写入 PostgreSQL 数据库的函数.我需要使其免受 SQL 注入的影响，但我不知道该怎么做.

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.

从 pg_query_params 组装的查询部分不会被注入(或者我被告知)，但是组装查询的另一部分通过 PHP 的字符串连接 .显然容易受到注射.

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;
}

如何防止 PHP 中的 SQL 注入?似乎没有解决我的担忧.

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

我正在使用 PostgreSQL 并试图找到与 pg_query_params

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

推荐答案

您可以使用 quote_ident()，然后再创建要执行的查询.你需要这样的东西:

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'));
?>

这篇关于在此 PHP 代码中防止 SQL 注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！