Question 160931 in PHP Question posted in
Official manual can be found here.

Can SQL Server statements address PHP ArrayIndex?

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
        ..."
Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search