Incorrect syntax near ‘@P1’., when trying to execute prepared statements frm php

Question

Post reply

Incorrect syntax near ‘@P1’., when trying to execute prepared statements frm php

SqlServerCampbell

Old Hand

Points: 384
More actions
February 5, 2020 at 5:42 pm

#3723403
Hi,

I looked all over the web, and there are different opinions about this error. It's difficult to find out which might be relevant to our problem. This is partly outside of the scope of this forum, but I'll try. This is 2008 R2 ; we will be moving to 2016, this year.

There error I'm getting is
Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 102 => 102 [2] =>
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near
‘@P1’. [message] => [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near ‘@P1’.
There IS no P1 variable or any other reference in our stored procedure or php code.

I replaced our current sqlsrv_query() call to a stored procedure (which works fine)
```
$spSQL = "{call p_1A_insert ( ?, ?, ?, ?, ?, .....
$storedProc = sqlsrv_query( $queryLink, $spSQL, $spParams );
```
, with
```
$sql_prep = "EXEC p_1A_insert( ?, ?, ?, ?, ?, .....
$stmt = sqlsrv_prepare( $queryLink, $sql_prep, $spParams ) ;
$storedProc = sqlsrv_execute( $stmt ) ;
```
I know this isn't a php forum. But does anyone have a suggestion, about what else we might try? We're getting Web Vulnerability flags, even though our input is sanitized.

Let me know if I need to paste parts of the Stored Procedure. As mentioned above, it works fine using sqlsrv_query().

Regards,

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

SqlServerCampbell Old Hand Points: 384 More actions · Answer 2

Ok, thanks SSC.

I also ran through it in Python, after creating a mini-table with only 5 fields, and a corresponding INSERT Stored Procedure.

Same result. I tried both Driver 11, and Driver 17.

frederico_fonseca SSCoach Points: 16017 More actions · Answer 3

did you try

$spSQL = "exec p_1A_insert ( ?, ?, ?, ?, ?, .....

$storedProc = sqlsrv_query( $queryLink, $spSQL, $spParams );

or

$spSQL = "exec p_1A_insert @parametername1 = ?, @parametername2 = ?, ?, ?, ?, .....

$storedProc = sqlsrv_query( $queryLink, $spSQL, $spParams );

e.g. do not use the ODBC style call

SqlServerCampbell Old Hand Points: 384 More actions · Answer 4

The sqlsrv_query call, that we were originally using, works fine.

The problem is

$stmt = sqlsrv_prepare($queryLink, $sql_prep, $spParams ) ;
$storedProc = sqlsrv_execute($stmt) ;

The sqlsrv_prepare() call returns a valid object. But the sqlsrv_execute call, returns the error.

We're trying to use fully prepared statements. sqlsrv_query doesn't prepare the parameters. It just sends them directly to the engine.

The @P1 variable is actually mentioned here,

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-prepare-transact-sql?view=sql-server-ver15

, in an example of a call to mssql's prepare and execute Stored Procedure.


DECLARE @P1 int;  
EXEC sp_prepare @P1 output,   
    N'@P1 nvarchar(128), @P2 nvarchar(100)',  
    N'SELECT database_id, name FROM sys.databases WHERE name=@P1 AND state_desc = @P2';  
EXEC sp_execute @P1, N'tempdb', N'ONLINE';  
EXEC sp_unprepare @P1;

So, it makes me think that looking for these System Stored Procedures, or through Microsoft's sqlsrv_execute function, might reveal something. But I'm not sure if the source is available, for each of these.

Also, we tried pairing down the mini stored procedure, and removing the OUTPUT variable. But we get the same result.

So whenever we mix the Prepared Statements with a call to a Stored Procedure, we get that error.

SqlServerCampbell Old Hand Points: 384 More actions · Answer 5

Got it. I can't believe it.

It's the absence of braces.

The call to the Stored Procedure should be written


$sql_prep = "{ p_1A_insert( ?, ?, ?, ?, ?, .....

rather than

$sql_prep = "p_1A_insert( ?, ?, ?, ?, ?, .....

As you can see above, we had those braces in the original call to the Stored Procedure, with sqlsrv_query(). But we left them out, when applying the call to the prepared statement.

Sorry for taking up digital real estate with something that is primarily a php problem. But perhaps someone will have a similar problem, at some point in the future.

Thanks for everyone's assistance on this. Everything is fine, now.

frederico_fonseca SSCoach Points: 16017 More actions · Answer 6

have a look at https://www.php.net/manual/en/function.sqlsrv-prepare.php

or https://docs.microsoft.com/en-us/sql/connect/php/sqlsrv-prepare?view=sql-server-ver15

different functions than the one you gave the link for - this may work for you

SqlServerCampbell Old Hand Points: 384 More actions · Answer 7

Frederico,

Thanks but that page doesn't call Stored Procedures. That illsutrates parameterized queries with straight UPDATEs or INSERTs. We don't have any problem doing that.

Anyway, our issue is resolved. It's because we were missing the braces {} surrounding our sql. We had those braces with the original sqlsrv_query() call. But for whatever reason, we didn't include them in the call to sqlsrv_prepare().

Thanks for the tip, we're good here.

Regards,