PostgreSQL query works but not with php

Question

I want to make a php query to a PostgreSQL database. I tested the query in the server and returns, but when I try it in php:

<?php

//Check the input
if (!isset($_POST['variable']))
echo "Address not selected";

$input = $_POST['variable'];
//$input = ucfirst(trim($_POST['variable']));
//echo $input;


$conn = pg_connect("host=localhost port=5432 dbname=geocoder user=postgres");
if (!$conn)
echo "Could not connect to server..";

$sql = "SELECT (addy).stateabbrev FROM geocode($input);";
$result = pg_query($conn, $sql);

if  (!$result)
    echo "Query did not executed..";

?>

I get that "Query did not executed..";

The string for the QUERY is taken from a html page using javascript.

In the error.log of Apache2 i get:

PHP Warning: pg_query(): Query failed: ERROR: syntax error at or near "Penn"\nLINE 1: SELECT (addy).stateabbrev FROM geocode(O

What can be the point here?

you should be using a placeholder in the SQL rather than interpolation, and you should be verifying/sanitizing the input. — Matt Whipple, Jan 19 '13 at 14:55
You are experiencing a SQL injection. Learn about that now and first: http://bobby-tables.com/ — hakre, Jan 19 '13 at 14:55
possible duplicate of [Preventing SQL injection in PHP](http://stackoverflow.com/questions/6725520/preventing-sql-injection-in-php) — hakre, Jan 19 '13 at 14:58
Using $input = pg_escape_string($_POST['variable']); didn't helped — GeoGraphGIS, Jan 19 '13 at 15:19
You might want to look into adding error checking to your code. It's real easy in php and very useful when things don't work. Assuming you've gotten an input, assuming you've connected to the db, and assuming you got a result back are all bad form. Check each time for an error. — Scott Marlowe, Jan 19 '13 at 22:41
Thanks all for the comments, but I founded the problem. $input must be inside single quotations. — GeoGraphGIS, Jan 20 '13 at 11:32

score 0 · Answer 1 · answered Jan 19 '13 at 15:10

0

Sanitize the user supplied data:

$input = pg_escape_literal($conn, $input);
$sql = "SELECT (addy).stateabbrev FROM geocode($input);";

answered Jan 19 '13 at 15:10

Clodoaldo Neto

118,695
26
233
260

score 0 · Accepted Answer · answered Jan 20 '13 at 11:34

0

I managed to find the problem, which is the fact that $input variable from geocode() function, must be surrounded by single quotes:

geocode('$input')

:)

answered Jan 20 '13 at 11:34

GeoGraphGIS

66
5

PostgreSQL query works but not with php

2 Answers2