使用 PHP 形式将 WKT 和 SRID 中的几何图形插入到 SQL Server 2017 中

Question

我有一个PHP 7.3表单，要求用户输入名称，WKT和SRID。 我想将WKT和SRID上传到SQL Server 2017，以便它使用STGeomFromText在Upload_WKT_Test中创建几何对象：

<?php
if ($_SERVER['REQUEST_METHOD']=="POST") {
$wkt = $_POST['wkt'];
$srid = $_POST['srid'];
$name = $_POST['name'];
try {
$wktQuoted = $pdo->quote ($wkt);
//$wktQuoted = "'$wkt'";
$sql = "INSERT INTO Upload_WKT_Test (Name, GeomCol1) VALUES (:name, :wktGeom)";
$wktGeom1 = "geometry::STGeomFromText(";
$wktGeom = $wktGeom1."".$wktQuoted.", ".$srid.")";
//echo $wktGeom."<br><br>".$name."<br><br>";
$stmnt = $pdo->prepare($sql);
$theData = [':name'=>$name, ':wktGeom'=>$wktGeom];
$stmnt->execute($theData);
} catch(PDOException $e) {
echo "Error: ".$e->getMessage();
}
} else {
$wkt="";
$alignment="";
$srid="";
}
?>

我的初始化.php：

<?php
ob_start();
session_start();
try {
$pdo = new PDO( "sqlsrv:Server=localhostSQLEXPRESS;Database=devdb", "", "");
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$pdo->setAttribute( PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC );
//$pdo->setAttribute( PDO::ATTR_EMULATE_PREPARES, true );
}
catch( PDOException $e ) {  
//die( "Error connecting to SQL Server" );
//die(print_r($stmnt->errorInfo(), true));
echo "Error: ".$e->getMessage();
}  
$root_directory = "testwkt";
$from_email = "[e-mail]";
$reply_email = "[e-mail]";
include "php_functions.php";
?>

我知道通过表单接受用户输入的首选方法是使用参数化查询来防止SQL注入。 我相信错误消息的来源与 WKT 周围的引号没有进入 INSERT INTO 语句有关。 是否可以用引号 (WKT( 将输入表单中的文本值括起来并使用它来构建几何对象？

输入表格POINT(100 10)的WKT示例

示例 SRID0

示例名称Test

WKT 的几何形状(需要引号(：

geometry::STPointFromText('POINT (100 10)', 0)

产生的错误消息：

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]
A .NET Framework error occurred during execution of user-defined routine or
aggregate "geometry": System.FormatException: 24114: The label
geometry::STGeomFrom in the input well-known text (WKT) is not valid. 
Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING,
MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, 
CURVEPOLYGON and FULLGLOBE (geography Data Type only). 
System.FormatException: at
Microsoft.SqlServer.Types.OpenGisTypes.ParseLabel(String input) at
Microsoft.SqlServer.Types.WellKnownTextReader.ParseTaggedText(OpenGisType type) at 
Microsoft.SqlServer.Types.WellKnownTextReader.Read(OpenGisType type, Int32 srid) at 
Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid) at
Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s) .

Upload_WKT_Test表：

CREATE TABLE dbo.Upload_WKT_Test
( id int IDENTITY (1,1),
Name varchar(50),
GeomCol1 geometry );
GO

WKT - 已知文本 - 一种以文本格式表示几何对象(例如点、线、多边形(的方法。 更多信息在这里。

SRID - 空间参考系统标识符 - 表示坐标系的整数。 更多信息在这里。

有关在 SQLServer 和 Azure SQL 数据库中使用几何实例的详细信息，请参阅此处。

有关 STGeomFromText 的更多信息

Answer 1

您应该在T-SQL语句中包含geometry::STGeomFromText并绑定$wkt参数的值，而不使用PDO::quote：

<?php
if ($_SERVER['REQUEST_METHOD']=="POST") {
$wkt = $_POST['wkt'];
$srid = $_POST['srid'];
$name = $_POST['name'];
try {
$sql = "
INSERT INTO Upload_WKT_Test (Name, GeomCol1) 
VALUES (:name, geometry::STGeomFromText(:wkt, :srid))";
$stmnt = $pdo->prepare($sql);
$theData = [':name'=>$name, ':wkt'=>$wkt, ':srid'=>$srid];
$stmnt->execute($theData);
} catch(PDOException $e) {
echo "Error: ".$e->getMessage();
}
} else {
$wkt="";
$alignment="";
$srid="";
}
?>