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:43 pm

#3723404
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 6 posts - 1 through 5 (of 5 total)

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

@P1 is where between your app and sql it has parameterised a query

it looks a bit like "select * from sometable where somevalue=@p1" ,@p1='fred'

when you switch to a proc then this goes away? then stick with procs - less procedural cache impact and less query plans. and as a bonus you can make data layer changes without having to do a build and compile all of your web layer

MVDBA

SqlServerCampbell Old Hand Points: 384 More actions · Answer 2

Hi MVDBA,

Yes, we're already using a Stored Procedure, and it runs fine with sqlsrv_query. We just can't seem to execute it, with prepared statements.

@p1 is not referenced anywhere in the Stored Procedure, nor in the php code.

Regards,

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

@p1 is generated by sql and your sql driver for prepared statements it's not in PHP

MVDBA

SqlServerCampbell Old Hand Points: 384 More actions · Answer 4

Yes, I understand. I'm just saying I can't find a reference to it anywhere, using a global search in the project subfolders, which includes all the php and all the sql code.

If it's generated by the driver, it doesn't leave me with anywhere to go.

We're getting a Web Vulnerability with the current mode of operation that we're using. So I've altered the php code, and replaced our sanitize functions with the official php versions.

But I also want to use a Prepared Statement, when we actually perform the INSERT. The INSERT is in a stored procedure, and we can call it successfully with sqlsrv_query. But when we try to use Prepared Statements with that same Stored Procedure and parameters, we get that error.

Anyway, I don't think there's a solution. We might have to replace the Stored Procedure, with direct INSERT and Logging, and use Prepared Statements to execute that.

Thanks for trying.

Regards,

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

you will never find @p1 in your codebase.

easiest way to see it it to run a profiler trace from sal management studio and see the queries coming through - not sure what you will get , but most likely an sp_preparestatement and an execute of that statement - you will see @p1, @p2 etc all over the place

MVDBA