Request VAR's and put in postgresql query

geomecrazy · May 4, 2016, 7:52pm

Query failed: ERROR: parse error - invalid geometry
HINT: “POINT($l” <-- parse error at position 8 within geometry
when I hard code the variables it works but when I try to request them it fails.
can someone help me?

<?php // Connecting, selecting database $dbconn = pg_connect("host=localhost dbname=gfoo user=foo password=foo") or die('Could not connect: ' . pg_last_error()); // Performing SQL query $lon = $_REQUEST['lon']; $lat = $_REQUEST['lat']; $query = 'SELECT "name", "state" FROM "public".county WHERE ST_Contains(county.the_geom, ST_Transform(ST_GeomFromText(\'POINT($lon $lat)\', 4326),4269) )'; $result = pg_query($query) or die('Query failed: ' . pg_last_error()); // Printing results in HTML echo "\n"; while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) { echo "\t\n"; foreach ($line as $col_value) { echo "\t\t\n"; } echo "\t\n"; } echo "

$col_value

\n"; // Free resultset pg_free_result($result); // Closing connection pg_close($dbconn); ?>

astonecipher · May 4, 2016, 10:17pm

Use pdo…

geomecrazy · May 4, 2016, 11:04pm

Wouldn’t the outcome from the sql error still be a problem?

geomecrazy · May 5, 2016, 2:31am

I’m not familiar with PDO! Is there no fix for the way it is now ???

astonecipher · May 5, 2016, 2:46am

The problem is here:

[php]SELECT “name”, “state” FROM “public”.county
WHERE ST_Contains(county.the_geom,
ST_Transform(ST_GeomFromText(‘POINT($lon $lat)’, 4326),4269)[/php]

It is bad practice (to current standard) to drop variables into a sql query for a number of reasons.

You need to ensure that variables being used are the proper type to be dropped in, as well.

PDO would wrap the query and drop the variables in as the proper types for use and protect the database at the same time.