Why postgresql don't update query with php

Question

i have a problem with a pg_query() on a php code. When i send my request i have this error :

2015-02-10 16:57:16.793 Ambilly[658:191087] PHP Warning:  pg_query():  in C:\inetpub\www.blabla.com\stoun\modifbati.php on line 22

this is my code :

<?php
session_start();
//On inclue les librairies
include('connect.php');
//on regarde si il y a eu un envoi ou non
if(isset($_POST['objectid'])&&isset($_POST['hauteur'])&&isset($_POST['occupation'])&&isset($_POST['nbr_niveau'])&&isset($_POST['nbr_lot'])&&isset($_POST['observation'])){
$codeID = $_POST['objectid'];
$hauteur = $_POST['hauteur'];
$occupation = $_POST['occupation'];
$nbrNiveau = $_POST['nbr_niveau'];
$nbrLot = $_POST['nbr_lot'];
$observation = $_POST['observation'];
//echo $code;
$conn = pg_pconnect("host=localhost port=5432 dbname=xxxxx user=xxxx password=xxxx");
if (!$conn) {
    echo "An error occurred.\n";
    exit;
}
//on fait le fichier
$date = date("Y-m-d");

$result = pg_query($conn, "UPDATE `suivi_bati` SET `hauteur`='".$hauteur."', `occupation`='".$occupation."', `nbr_niveau`='".$nbrNiveau."', `nbr_lot`='".$nbrLot."', `observation`='".$observation."'  WHERE `objectid`='".$codeID."'");
if (!$result) {
    echo "UPDATE IMPOSSIBLE";
    echo pg_last_error();
    exit;
} else {
    echo "FICHE BATI MODIFIER";
 }
}
else {
    echo "Pas possible";
}
?>

Any idea ? Thanks Stoun

**Building SQL statements with outside variables makes your code vulnerable to SQL injection attacks.** Also, any input data with single quotes in it, like "O'Malley", will blow up your query. Learn about parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174) has many detailed examples. See also http://bobby-tables.com/php for alternatives & explanation of the danger. Running SQL code built with outside data is like eating soup made from ingredients found on your doorstep. — Andy Lester, Feb 12 '15 at 16:15

score 2 · Accepted Answer · answered Feb 11 '15 at 00:29

This query can't work:

$result = pg_query($conn, "UPDATE `suivi_bati` SET `hauteur`='".$hauteur."', `occupation`='".$occupation."', `nbr_niveau`='".$nbrNiveau."', `nbr_lot`='".$nbrLot."', `observation`='".$observation."'  WHERE `objectid`='".$codeID."'");

because backquotes characters around identifiers are syntactically invalid in PostgreSQL. Remove them, the names used in this query (lower-case ASCII) don't need quotes anyway.

Also pg_escape_string needs to be applied to variables coming from a POST, otherwise your query is vulnerable to SQL injection.

The fixed query could look like this, with sprintf:

$result = pg_query($conn,
           sprintf("UPDATE suivi_bati SET
                hauteur='%s',
                occupation='%s',
                nbr_niveau='%s',
                nbr_lot='%s',
                observation='%s'
            WHERE objectid='%s'",
              pg_escape_string($hauteur),
              pg_escape_string($occupation),
              pg_escape_string($nbrNiveau),
              pg_escape_string($nbrLot),
              pg_escape_string($observation),
              pg_escape_string($codeID))
          );

or with the more modern pg_query_params, which doesn't need explicit escaping nor single quotes around literals:

$result = pg_query_params($conn,
           "UPDATE suivi_bati SET
                    hauteur=$1,
                    occupation=$2,
                    nbr_niveau=$3,
                    nbr_lot=$4,
                    observation=$5
            WHERE objectid=$6",
              array($hauteur,
              $occupation,
              $nbrNiveau,
              $nbrLot,
              $observation,
              $codeID)
          );

pg_query_params() makes programming so much easier! – Frank Heikens Feb 11 '15 at 06:52 — Frank Heikens, Feb 11 '15 at 06:52
Thanks for the answer, it's ok with pg_query_params. – Stoun Feb 11 '15 at 15:14 — Stoun, Feb 11 '15 at 15:14

Why postgresql don't update query with php

1 Answers1