update query php postgres failing for no reason

Question

I have this piece of code in PHP and using a PostgreSQL as the database. I am getting all the parameters from the GET. Have checked them by printing it. The formed query executes on a Postgres terminal but fails from the PHP script.

Here is the piece of code.

<?php

$link = pg_connect("host=localhost dbname=postgres user=postgres password=password") or die('connection failed');

# Building the query
$newq=sprintf("update purchase_info set ....... comments=%s where id=%s",......,$comm,$id);

    print $newq; // This query runs on the postgres terminal
    $query=addslashes($newq); // to escape "" as one of my fields is comments
    $result=pg_query($link,$newq);

    if (!$result) {
        echo "An error occured.\n";
    }
    pg_close($link);
?>

Other queries run in the same script. This SQL statement has about 14 field being updated.

What Is going wrong hear. Appreciate the help!

Nothing fails "for no reason".

Brad
– Brad

2012-04-19 21:19:03 +00:00
Commented Apr 19, 2012 at 21:19 — Brad
– Brad, Commented Apr 19, 2012 at 21:19

mu is too short · Accepted Answer · 2012-04-19 21:20:17Z

5

You shouldn't be using addslashes to quote strings for PostgreSQL, you should use pg_escape_literal:

pg_escape_literal() escapes a literal for querying the PostgreSQL database. It returns an escaped literal in the PostgreSQL format. pg_escape_literal() adds quotes before and after data. Use of this function is recommended instead of pg_escape_string().

You should never use addslashes for quoting strings for a database:

It's highly recommended to use DBMS specific escape function (e.g. mysqli_real_escape_string() for MySQL or pg_escape_string() for PostgreSQL)

You should be doing this:

$newq = sprintf("update purchase_info set ... comments=%s where id=%d", ..., pg_escape_literal($comm), $id);

I'm assuming that id is actually a number as well.

answered Apr 19, 2012 at 21:20

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mu is too short Over a year ago

@HiteshDharmadasani: I'd also recommend that you look into prepared statements, placeholders, and PDO so that you don't have to muck about with quoting things yourself.

Daniel Vérité Over a year ago

pg_escape_literal() is not yet available in any released version of php (5.4.0 being the latest currently). That's going to be a problem for most people!

mu is too short Over a year ago

@DanielVérité: Thanks, PHP isn't my usual environment.

mu is too short Over a year ago

@HiteshDharmadasani: Feel free to switch the accept to Daniel Vérité's if your PHP doesn't have pg_escape_literal.

Daniel Vérité · Accepted Answer · 2012-04-19 21:17:13Z

4

Assuming you really want to inject parameters into the SQL query, the correct code would be:

$newq=sprintf("update purchase_info set ... comments='%s' where id='%s'",
   pg_escape_string($comm), pg_escape_string($id));
// DO NOT USE to addslashes, it is not correct
$result=pg_query($link, $newq);

Notice the single quotes surrounding the %s in the format string. Also if id is an integer, it's better do use %d (no quotes) instead of '%s'

answered Apr 19, 2012 at 21:17

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

7 Comments

Hitesh Dharamdasani Over a year ago

All the security apart. will building my sql query like this $newq="update purchase_info set ... comments='".$comm."' where id='".$id."'"; work too?

mu is too short Over a year ago

You almost certainly shouldn't be quoting the id, PostgreSQL wants numbers to be numbers.

kgrittn Over a year ago

Actually, PostgreSQL treats a literals in single quotes as being of type "unknown" until it is forced to resolve it. In the absence of other information it will resolve to text, but you can generally use single-quoted literals in assignments of any type. This is done so that custom data types, like global coordinates, can be treated as first-class objects with values assigned from literals without needing to explicitly cast the literals.

mu is too short Over a year ago

@kgrittn: Fair enough. I'd still call "quoting everything in sight" a bad cargo-cult habit from doing too much MySQL.

Erwin Brandstetter Over a year ago

@kgrittn: While it certainly works to use a single-quoted literal for numbers it is generally not a good idea. If you have overloaded functions, like f(int) and f(text), PostgreSQL will assume the type text and pick the second function if you call f('1'), while it will pick the first (and fitting) one if you don't: f(1).

|

Collectives™ on Stack Overflow

update query php postgres failing for no reason

2 Answers 2

4 Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related