Jump to content


Photo

PHP pdo_odbc SQL Server Stored Procedure Unexpected Error

php sql sql-serber-2005 stored-procedures pdo

  • Please log in to reply
No replies to this topic

#1 MutatedVirus

MutatedVirus

    Member

  • Members
  • PipPip
  • 14 posts
  • LocationUK

Posted 11 November 2013 - 07:20 AM

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

 






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com