Address PHP ArrayIndex in SQL Server statement

niwlg2el 于 2023-05-12 发布在 PHP

关注(0)|答案(2)|浏览(88)

I am trying to execute a while loop in a SQL Server statement. This statement is placed in PHP and contains several PHP variables. I receive parsing error which does not makes any sense to me. I provide a array which contains strings. Further I point to my array value with a index, the output is a string. myArray[2] = "bird".

Still I receive a parsing error. I declare @CurrentValue as VARCHAR and also SET string content. Of course a better solution would be to perform the loop outside of SQL with PHP instead. Lets just say, I do no thave other possibilites to perform those task, like shown.

Any help or hint is higly appreciated. The error points exactly to my @CurrentValue declaration line.
Parse error: syntax error, unexpected string content "", expecting "-" or identifier or variable or number

My code looks like:

$myArray = ["cat", "dog", "bird"];
$myArrayLength = count($myArray);

$sql = "...
        DECLARE @PhpArrayIndex INT = 0
        DECLARE @PhpArrayLength INT = '$myArrayLength'

        WHILE @PhpArrayIndex <= @PhpArrayLength
        BEGIN
            DECLARE @CurrentValue VARCHAR(10) = '$myArray[' + @PhpArrayIndex + ']';

            INSERT INTO dbo.myTable (animals)
            VALUE (@CurrentValue)

            SET @PhpArrayIndex += 1;
        END
        ..."

sql-server

来源：https://stackoverflow.com/questions/76206410/address-php-arrayindex-in-sql-server-statement

2条答案

按热度按时间

5cnsuln71#

Your @CurrentValue declaration statement in your SQL code is causing trouble because you attempted to merge an integer value and a string, an invalid operation for SQL Server. Instead, try relying on the CONCAT function to bring these two values together.

modified SQL.

$myArray = ["cat", "dog", "bird"];
$myArrayLength = count($myArray);

$sql = "...
        DECLARE @PhpArrayIndex INT = 0
        DECLARE @PhpArrayLength INT = '$myArrayLength'

        WHILE @PhpArrayIndex < @PhpArrayLength
        BEGIN
            DECLARE @CurrentValue VARCHAR(10) = CONCAT('$myArray[', @PhpArrayIndex, ']');

            INSERT INTO dbo.myTable (animals)
            VALUES (@CurrentValue)

            SET @PhpArrayIndex += 1;
        END
        ..."

I made a tweak to the WHILE condition by swapping <= with <, owing to the PHP array's starting index of 0. Meanwhile, to add @CurrentValue to the table, I utilized the VALUES keyword in lieu of VALUE in the INSERT statement.

赞(0）回复(0）举报 2023-05-12

x8goxv8g2#

Your primary issue is that you are injecting your data directly in to your query. Never do this, it is dangerous and can cause injection attacks.

You should use a Table Valued Parameter for this.

First declare a table type in your database, from SSMS

CREATE TYPE dbo.StringList (value varchar(10) NOT NULL);

Then pass it through

$myArray = [["cat"], ["dog"], ["bird"]];
$params = [
    [["StringList" => $myArray, "dbo"]],
];

$sql = "
INSERT INTO dbo.myTable (animals)
SELECT value
FROM ? AS t;
";

Note that $myArray is a doubly-nested jagged array.

赞(0）回复(0）举报 2023-05-12

我来回答

Address PHP ArrayIndex in SQL Server statement

2条答案

相关问题

热门标签

最新问答