0

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?

Spatial Digger
  • 1,883
  • 1
  • 19
  • 37
  • remove double quotes arounf variable – Vao Tsun Apr 17 '17 at 20:30
  • 1
    Your code is vulnerable to [**SQL injection attacks**](https://en.wikipedia.org/wiki/SQL_injection). You should use [**pgsql**](http://us3.php.net/manual/en/function.pg-prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php) prepared statements with bound parameters as described in [**this post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). – Alex Howansky Apr 17 '17 at 20:33
  • ok, so trying to get my head around this http://php.net/manual/en/function.pg-execute.php – Spatial Digger Apr 17 '17 at 20:44

2 Answers2

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));
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
  • 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 Apr 17 '17 at 21:10
  • var dump shows the query: string(103) "SELECT "Publications"."Pub_ID", "Publications"."ART_TITEL" FROM "Publications" where "Pub_ID"=$post_pub" – Spatial Digger Apr 17 '17 at 21:11
  • Ok, solved, I did change $Pub_ID to $1, I assume you can't repeat variables here. – Spatial Digger Apr 17 '17 at 21:15
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.

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132