Php postgresql variables in a query

Question

I'm trying to get the right syntax for the following. In this case $post_pub = 1

$sql='SELECT "Publications"."Pub_ID", "Publications"."ART_TITEL" FROM "Publications" where "Pub_ID"="$post_pub"';

Php throws an error: column "$post_pub" does not exist

I've stumbled across pg_query_params, this feels like the right direction, but I need some help. How can I get this to work?

Your code is vulnerable to SQL injection attacks. You should use pgsql or PDO prepared statements with bound parameters as described in this post. — Alex Howansky
– Alex Howansky, Commented Apr 17, 2017 at 20:33
ok, so trying to get my head around this php.net/manual/en/function.pg-execute.php — Spatial Digger
– Spatial Digger, Commented Apr 17, 2017 at 20:44

Oto Shavadze · Accepted Answer · 2017-04-17 20:56:33Z

3

I never used pg_connect though I think you need something like this:

$sql='SELECT "Publications"."Pub_ID", "Publications"."ART_TITEL" 
FROM "Publications" 
where "Pub_ID"=$1 ';


$result = pg_query_params($dbconn, $sql, array($post_pub));

answered Apr 17, 2017 at 20:56

Oto Shavadze

43.3k56 gold badges168 silver badges248 bronze badges

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

3 Comments

Spatial Digger Over a year ago

Warning: pg_query_params(): Query failed: ERROR: syntax error at or near "$" LINE 1: ...ns"."ART_TITEL" FROM "Publications" where "Pub_ID"=$post_pub ^ in ...

Spatial Digger Over a year ago

var dump shows the query: string(103) "SELECT "Publications"."Pub_ID", "Publications"."ART_TITEL" FROM "Publications" where "Pub_ID"=$post_pub"

Spatial Digger Over a year ago

Ok, solved, I did change $Pub_ID to $1, I assume you can't repeat variables here.

Vao Tsun · Accepted Answer · 2017-04-17 20:57:56Z

0

the problem is double quotes around variable. Postgres understands it as "database object" name, in this part of query, a column. to avoid it, try using:

$sql='SELECT "Publications"."Pub_ID", "Publications"."ART_TITEL" FROM "Publications" where "Pub_ID"='."$post_pub";

also consider moving to PDO - such usage is a straight invitation for sql injection. Setting$post_pub to 0 or (delete from Publications)" will delete all data if user has enough right, for example.

edited Apr 17, 2017 at 20:57

answered Apr 17, 2017 at 20:31

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

5 Comments

Spatial Digger Over a year ago

after changing this error: Warning: pg_query(): Query failed: ERROR: syntax error at or near "$" LINE 1: ...ns"."ART_TITEL" FROM "Publications" where "Pub_ID"=$post_pub ^ in ...

Spatial Digger Over a year ago

Do I need to use pg_prepare and pg_execute? I'm a noob to postgresql after using mysql for a number of years.

Vao Tsun Over a year ago

@GaryNobles yes

Spatial Digger Over a year ago

Thank you for editing the code, I'm using pg_connect not PDO, the code isn't working, throws a http 500 error.

Vao Tsun Over a year ago

the @Oto's advice is preferable - use pg_query_params

Collectives™ on Stack Overflow

Php postgresql variables in a query

2 Answers 2

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related