To sanitize all user's input in PostgreSQL by PHP

php, postgresql, sanitize

Solution

According to the Postgres documentation on `pg_prepare`, all escaping is done for you. See the examples section where it lists the following code (including the comments):

<?php
// Connect to a database named "mary"
$dbconn = pg_connect("dbname=mary");

// Prepare a query for execution
$result = pg_prepare($dbconn, "my_query", 'SELECT * FROM shops WHERE name = $1');

// Execute the prepared query.  Note that it is not necessary to escape
// the string "Joe's Widgets" in any way
$result = pg_execute($dbconn, "my_query", array("Joe's Widgets"));

// Execute the same prepared query, this time with a different parameter
$result = pg_execute($dbconn, "my_query", array("Clothes Clothes Clothes"));
?>

Though it may be useful to note that they use single quotes (`'`) instead of double quotes (`"`) around the query string, as then `$1` won't accidentally get interpolated into the string.

Problem

This question is based on this thread. Do you need the explicit sanitizing when you use pg_prepare? I feel that pg_prepare sanitizes the user's input automatically such that we do not need this ``` $question_id = filter_input(INPUT_GET, 'questions', FILTER_SANITIZE_NUMBER_INT); ``` Context where I use Postgres ``` $result = pg_prepare($dbconn, "query9", "SELECT title, answer FROM answers WHERE questions_question_id = $1;"); $result = pg_execute($dbconn, "query9", array($_GET['question_id'])); ```

Original source