Jump to content

PHP pdo_odbc SQL Server Stored Procedure Unexpected Error


MutatedVirus

Recommended Posts

I'm having a problem with PHP and a stored procedure in SQL Server 2005. I haven't really touched stored procedures before and haven't used them with php. I can't retrieve a value from a table that I know is definitely there. I have a simple login table, what I want the stored procedure to do is to take a username as an input and then output that user's password salt. I want the stored procedure to use parameterised values to avoid SQL injection. What happens at the moment is when the php page is run, nothing is returned and the SQL Profiler displays the following error:

User Error Message: Incorrect Syntax near '@Username'

While this error message should tell me something is wrong, I can't for the life of me see where the incorrect syntax is.

Copy of the Php:

$user = "usercm";
$password ="cmuserpassword";
try{$conn = new PDO("odbc:DRIVER={SQL Server Native Client 10.0};SERVER=TestingServer;DATABASE=TestingDatebase;",$user,$password);}
catch(PDOException $e){echo "oh no";}

$username = "sdct";

$prepusp = $conn->prepare("EXEC uspReturnSalt(?,?)");
$prepusp->bindParam(1, $username, PDO::PARAM_STR);
$prepusp->bindParam(2, $usersalt, PDO::PARAM_STR, 450);
$prepusp->execute();

Copy of the Stored Procedure:

ALTER PROCEDURE [dbo].[uspReturnSalt]
@Username NVARCHAR(100),
@Usersalt NVARCHAR(450) OUTPUT
AS
BEGIN
DECLARE @sqlcmd NVARCHAR(MAX);
DECLARE @params NVARCHAR(MAX);
SET @sqlcmd = N'SELECT @Usersaltone = salt FROM CMUsers WHERE username = @Usernameone';
SET @params = N'@Usernameone NVARCHAR(100), @Usersaltone NVARCHAR(450) OUTPUT';
EXECUTE sp_executesql @sqlcmd, @params, @Usernameone = @Username, @Usersaltone = @Usersalt OUTPUT;
END

To clarify, the server this is on runs Windows 2003 so I cannot use the sqlsrv drivers as they require SQL Server Native Client 2012 which is incompatible. It is impossible to upgrade the operating system (server isn't mine) so I can't use any php drivers that require SQL Server Native Client 2012.

If anyone could help I would be eternally grateful.

Here is the sql profiler messages before and after that error:

RPC:Completed | exec [sys].sp_sproc_columns_90 N'uspReturnSalt' ,@ODBCVer=3

RPC:Starting | declare @p1 int
set @p1 =NULL
exec sp_prepare @p1 output,N'@Username nvarchar(100),@P2 text OUTPUT' ,N'EXEC uspReturnSalt(@Username,@P2 OUTPUT)' ,1
select @p1


Exception | Error: 102, Severity: 15, State: 1

User Error Message | Incorrect syntax near '@Username'.

SP:CacheMiss | (@Username nvarchar(100),@P2 text OUTPUT)EXEC uspReturnSalt(@Username,@P2 OUTPUT)

Exception | Error: 8180, Severity: 16, State: 1

User Error Message | Statement(s) could not be prepared

RPC:Completed | declare @p1 int
set @p1=NULL
exec sp_prepare @p1 output, N'@Username nvarchar(100),@P2 text OUTPUT' ,N'EXEC uspReturnSalt(@Username,@P2 OUTPUT)',1
select @p1

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.