Updating with Postgresql

Question

For the life of me, i've been working with this for so long, i've done many updates that have worked, but for some reason, it won't work with this bit of code:

If anyone can see anything wrong with it, please help me out. I'm a little stuck:

if (isset($_POST['membership'])){

    $accTypeID = getAccTypeID($db, $_POST['membership']);
    $res = pg_query("SELECT * FROM accounts where username='" .$_SESSION['username']. "'");
    $rec = pg_fetch_row($res);
    if($res > 0){
        pg_query("UPDATE accounts SET paymentdate ='" .date("Y-m-d"). "', 
        accounttype_id=$accTypeID 
        WHERE username='" .$_SESSION['username']. "'");
        $p->addContent('Purchasing has succeeded.');
    }

}

I run this with my website and it says it works, but when i check my database, nothing has been updated and my console produces this error:

pg_query(): Query failed: ERROR: syntax error at or near "WHERE"\nLINE 3: WHERE username='anuj'

The first step in debugging such a problem is to print the complete generated statement. In 90% of the cases you will notice the error yourself immediately. — , Sep 19 '13 at 21:29
Presumably your `$accTypeId` is empty which leads to a syntax error just before the WHERE. You should use `pg_query_params` or some similar method that can deal properly with parameters instead of injecting them without any check or quoting. — Daniel Vérité, Sep 19 '13 at 21:32
Agree with horsey, and it's usually a case of forgetting to include a space or neglecting to balance parentheses or close quotes. — David Aldridge, Sep 19 '13 at 21:53
Cheers man, you're right, my accTypeID was giving me an empty. I recoded the function to make it do the right job. Cheers for the help — Anuj Hari, Sep 19 '13 at 21:56
If you switched to placeholders instead of string interpolation and concatenation, your code would be a lot more readable, less error prone, ... — mu is too short, Sep 19 '13 at 22:26
Aggh this is totally dangerous, read about SQL injection https://en.wikipedia.org/wiki/SQL_injection — leonbloy, Sep 19 '13 at 22:40
**By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. — Andy Lester, Sep 20 '13 at 04:58

score 0 · Answer 1 · answered Nov 17 '13 at 08:41

Use parameterized queries. Your problem here is that $accTypeID is probably an empty value or undefined. If this happens then setting the username to bob' or true will result in the purchase always succeed.

Always use parameterized queries. It will also correct things like this since an undef should be treated as NULL or at least ''

Updating with Postgresql

1 Answers1