PHP - PostgreSQL error updating table

Question

I fetch some values from a database and display them on some textfields. When I change one specific value and try to store it back to the database, it's done properly. But when I try to do the same with any other value from any other textfield, I get the error "syntax error at or near where". Any thoughts?

'UPDATE table1 SET "intcolumn"='. $value .', "stringcolumn"=\''.  $value2.'\''.' WHERE "column2"='.$value3);

Update on intcolumn is done properly. On stringcolumn I get the error, even if I update only stringcolumn

Use prepared statements and the problem will go away. echoing the query will make the error easy to spot. Also, you are open to SQL injections — hank
– hank, Commented Oct 15, 2013 at 7:32
I know about SQL injection. I'm not concerned with it right now. Thanks. I'll try ti — GsetIT
– GsetIT, Commented Oct 15, 2013 at 7:37
BE concerned with SQL injection because you don't know how to escape values properly. It's fiddly and error-prone to do it yourself which is why almost every person who sees this sort of code says to use prepared statements. — Richard Huxton
– Richard Huxton, Commented Oct 15, 2013 at 9:11
Yes I know. I meant right now the page is offline and under development and there are other priorities. Thanks! — GsetIT
– GsetIT, Commented Oct 15, 2013 at 10:09

ChrisKnowles · Accepted Answer · 2013-10-18 14:48:14Z

2

Changing your apostrophes to quotes and putting your values inside delimiters will help readability. This should make debugging easier, and easier to spot rather than having to escape characters etc.

pg_query($db, "UPDATE table1 SET intcolumn={$value}, stringcolumn='{$value2}' WHERE column2={$value3}");

A better approach would be to use pg_query_params and let postgres worry about escaping characters, and will stop injection attacks.

$params = array($value, $value2, $value3);
pg_query_params($db, "UPDATE table1 SET intcolumn=$1, stringcolumn=$2 WHERE column2=$3", $params);

answered Oct 18, 2013 at 14:48

ChrisKnowles

7,2003 gold badges16 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

PHP - PostgreSQL error updating table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related