0

Im getting syntax error with queries in pg with PHP. Im not clear about how should i write tables name and variables in queries. Actually im using "TABLE_NAME" and '$var_name' inside of queries. Specifically on this code i got:

Warning: pg_query(): Query failed: ERROR: syntax error at or near "$" LINE 2: "Nombre_Prod" = COALESCE($newNombre_Prod, "Nombre_Pr...

For this query:

UPDATE public."Products" SET
        "Name_Prod" = COALESCE($newName_Prod, "Name_Prod"),
        "Price_Prod" = COALESCE($newPrecice_Prod, "Price_Prod"),
        "Cant_Prod" = COALESCE($newCant_Prod, "Cant_Prod"),

    WHERE Prod_ID = $id
    AND ($newNamee_Pro IS NOT NULL AND $newName_Prod IS DISTINCT FROM Name_Prod OR
        $newPrice_Prod IS NOT NULL AND $newPrice_Prod IS DISTINCT FROM Price_Prod OR
        $newCant_Prod IS NOT NULL AND $newCant_Prod IS DISTINCT FROM Cant_Prod OR
        )

Here's my code:

$id = $_GET['id'];

$newName_Prod = "'".$_POST['newName_Prod']."'";
$newPrice_Prod = "'".$_POST['newPrice_Prod']."'";
$newCant_Prod = "'".$_POST['newCant_Prod']."'";

$query = 
   'UPDATE public."Products" SET
       "Name_Prod" = COALESCE($newName_Prod, "Name_Prod"),
       "Price_Prod" = COALESCE($newPrecice_Prod, "Price_Prod"),
       "Cant_Prod" = COALESCE($newCant_Prod, "Cant_Prod"),

   WHERE Prod_ID = $id
   AND ($newNamee_Pro IS NOT NULL AND $newName_Prod IS DISTINCT FROM Name_Prod OR
       $newPrice_Prod IS NOT NULL AND $newPrice_Prod IS DISTINCT FROM Price_Prod OR
       $newCant_Prod IS NOT NULL AND $newCant_Prod IS DISTINCT FROM Cant_Prod OR
       )';

$result = pg_query($conexion,$query);
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
myge32
  • 75
  • 8
  • Does this answer your question? [How to use prepared statements with Postgres](https://stackoverflow.com/questions/1247373/how-to-use-prepared-statements-with-postgres) – ADyson Jul 06 '20 at 23:24
  • Does this answer your question? [What is the difference between single-quoted and double-quoted strings in PHP?](https://stackoverflow.com/questions/3446216/what-is-the-difference-between-single-quoted-and-double-quoted-strings-in-php) – catcon Jul 07 '20 at 00:05
  • @catcon that's not an appropriate solution here. OP should be using prepared statements and parameterised queries. String concatenation is an unsafe way to include external data in a SQL query - it exposes the database to SQL Injection vulnerabilities – ADyson Jul 07 '20 at 06:30

0 Answers0