Using PDO for an insert and the_geom

2019-08-19 03:39发布

问题:

I'm switching my code to PDO for increased security. My insert works until I add a special column that create spatial data. See below for the standard insert that works, and 2nd below for what is not working.

$sql = "INSERT INTO sites_tbl (sitename, the_geom) VALUES ('$_POST[sitename]', st_geomfromtext('POINT($geomstring)',27700))";

The geomstring = a number formatted 000000 000000

Using PDO the same insert looks something like (below) this works if I just want to insert the sitename, but not when I do the_geom. The value 325123 215432 will eventually be a variable, but for now I'm testing list this.

  $stmt5 = $conn ->prepare(
"INSERT INTO sites_tbl (sitename, river_id, group_id, accepted_site, the_geom, bmwp_threshold) VALUES (?, ?, ?, ?, ?, ?)");

$stmt5->bindParam(1, $sitename);
$stmt5->bindParam(2, $river_id);
$stmt5->bindParam(3, $group_id);
$stmt5->bindParam(4, $accepted_site);
$stmt5->bindParam(5, $geomstring3);
$stmt5->bindParam(6, $bmwp_threshold);

$geomstring2 = "'POINT(635230 352120)'";
$geomstring3 = st_geomfromtext($geomstring2, 27700);

回答1:

you cannot
bind
an arbitrary
SQL part
using
prepared
statement

but string
or numeric
literal
only.

$geomstring4 = "'POINT(325123 215432)'";

$stmt5 = $conn ->prepare(
   "INSERT INTO sites_tbl (sitename, the_geom) VALUES (?, st_geomfromtext(?,27700)))");
$stmt5->bindParam(1, $sitename);

$stmt5->bindParam(2, $geomstring4);