使用PHP表单将WKT和SRID中的几何体插入SQL Server 2017

Question

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

<?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=localhost\SQLEXPRESS;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）包围输入表单中的文本值，并使用它构建几何体对象？

示例WKT进入表格点（100 10）

示例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) .

上传WKT测试表：

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

WKT-众所周知的文本-一种以文本格式表示几何对象（例如点、线、多边形）的方法。更多信息请点击这里。

SRID—空间参考系标识符—表示坐标系的整数。更多信息请点击这里。

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

更多关于STGeomFromText的信息

Answer 1

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

<?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="";
    }
?>