locked
Need help with PDO::quote() and PDOStatement::bindValue and PDO::execute using new SQLSRVR 2.0 driver RRS feed

  • Question

  • We are using PHP 5.3 on a IIS server accessing a SQL Server 2005 database.  All DML transactions in our PHP application our done via stored procedures. 

    Here is the code we are having trouble with:

     

    <?php 
    
    	$uid = "***";
    
    	$passwd = "****";
    
    	$server = '****';
    
    	$db = "***";
    
    	
    
    	$conn = new PDO("sqlsrv:server=$server; database=$db", $uid, $passwd);
    
    	
    
    	if($conn) {
    
    		echo "<h1>Db connected</h1>";
    
    	}
    
    	else {
    
    		echo "<pre>";
    
    		print_r(sqlsrv_errors());		
    
    	}
    
    	
    
    	$paramArray = array();
    
    	$paramArray['mode'] 			= "V";
    
    	$paramArray['form_id'] 		= "101";
    
    	$paramArray['form_name'] 		= "Modelo's";
    
    	$paramArray['form_name_display'] 	= "Modelo form";
    
    	$paramArray['form_comments'] 		= "";
    
    	
    
    	$objPdo = executeProc("proc_forms",$paramArray,$conn);
    
    	$result = fetch_records($objPdo);
    
    	
    
    	function executeProc($sp_name,$paramArray,$objDb="") {
    
    		$sp_stmt = "EXEC ".$sp_name." ";
    
    		$paramString = "";
    
    		
    
    		foreach($paramArray as $k=>$v) {
    
    			$param = ":".$k.", ";		
    
    			$paramString .= $param;
    
    		}		
    
    		$paramString = substr($paramString,0,-2);
    
    		$paramString = $sp_stmt.$paramString;			
    
    		try {
    
    			$procInit = $objDb->prepare($paramString);	//prepares procedure
    
    			foreach($paramArray as $key=>&$value) {
    
    				$newValue = $objDb->quote($value);
    
    				$procInit->bindParam($key,$newValue);
    
    			}
    
    			try{$procInit->execute();}catch(PDOException $e) {
    
    				echo $e->getMessage();
    
    			}
    
    			return $procInit;
    
    		}
    
    		catch(PDOException $ex) {
    
    			$msg = "Couldn't prepare statement::".$ex->getMessage();
    
    			$displayDbError($msg);			
    
    		}
    
    	} //eof executeProc
    
    	
    
    	function fetch_records(&$procInit) {
    
    		try{
    
    			$result = $procInit->fetch(PDO::FETCH_ASSOC);
    
    			return $result;
    
    		}
    
    		catch(PDOException $ex) {
    
    			$msg = "Can't execute procedure::".$ex->getMessage();
    
    			$displayDbError($msg);
    
    		}		
    
    	}
    
    	
    
    	function displayDbError($msg) {
    
    		echo "<label class='dbErrLabel'>".$msg."<label>";
    
    	}
    
    	
    
    	
    
    	
    
    //------------------------------------------------------------------
    
    

    Specifically, the execute fails if we leave the $objDb->quote($value) in.  If instead we comment that line out, process the $value with a substring or preg_replace to get rid of the single quote in the data, then the execute succeeds.

    We have the latest version of the drivers (php_pdo_sqlsrv_53_nts_vc9.dll = 2.0.1802.200, & php_sqlsrv_53_nts_vc9.dll = 2.0.1802.200 )

    We are able to connect, and, if we process the string $value with some other method beside PDO::quote(), the script executes successfully.

    Please if anyone has any suggestions??

     


    Bruce Amick at bbvany dot com
    Thursday, April 28, 2011 2:36 PM

Answers

All replies

  • Bruce-

    Are you able to get error information when the execute fails? That might be helpful in understanding what's going on here. I was able to execute your script successfully with the $objDb->quote($value) line in place. Could you share your stored procedure? (I made up a simple one to use with your script...wondering if the problem lies there.)

    Thanks.

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    Thursday, April 28, 2011 5:21 PM
  • Hi Brian,

    The error messages we were getting were not helpful.  We were getting an erray with 0000, and then blanks for the other two elements.

    Here's the sproc that corresponds with the code above.  It's actually our simplest stored procedure in this app.

    CREATE PROCEDURE [dbo].[proc_forms]
    
    
    
    @mode varchar(1),
    
    @form_id varchar(11),
    
    @form_name nvarchar(50),
    
    @form_name_display nvarchar(50),
    
    @form_comments text
    
    
    
    AS
    
    declare @sql nvarchar(500)
    
    IF @mode = 'V' 
    
    BEGIN
    
    	set @sql = 'SELECT form_id,
    
    	form_name,
    
    	form_name_display,
    
    	form_comments from tbl_forms where 1=1'
    
    
    
    if @form_name!='' 
    
    begin	
    
    	set @sql = @sql + ' and form_name ='''+@form_name+''''
    
    end
    
    if @form_name_display!='' 
    
    begin
    
    	set @sql = @sql + ' and form_name_display like ''%'+@form_name_display+'%'''
    
    end
    
    --SELECT @sql
    
    EXEC (@sql)
    
    END;
    
    
    
    

    Honestly,  we've been experiencing a good deal of frustration in trying to migrate onto this driver and use it within the PDO context.  Just finished dealing with a problem on using the sqlsrvr driver within a session manager class.  solved thanks to a forum posted by Robert Johnson in May of last year.

    Any help you can give is greatly appreciated.


    Bruce Amick at bbvany dot com
    Monday, May 2, 2011 5:44 PM
  • Bruce-

    Thanks for the stored procedure info...that has been helpful. I too am seeing a problem with the PDO::quote method, but I don't (unfortunately) have a work around at the moment. I've reduced the problem to a simple repro and passed it along to the dev team. I'll follow up.

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    Monday, May 2, 2011 11:51 PM
  • Hi Bruce

    Thanks for the note on the session management solution.  The post is far down the page and not marked as the answer, so I'm glad you found it.  It's here for anyone else who wants to view it: http://social.msdn.microsoft.com/forums/en-us/sqldriverforphp/thread/C2ED0719-79E7-4822-88A2-1057A9E2C459

    The SQL Server version of PDO::quote() places quotes at the beginning and end of the text, exactly the same as the T-SQL function QUOTENAME(@param,'''') would.  It looks like this is correct according to the PDO specification...  on my computer it is working correctly...

    When you processed $value with other methods instead of PDO::quote(), did you add a quote to the beginning and end of the string?

    So is it possible to change your stored proc from this:   

    set @sql = @sql + ' and form_name ='''+@form_name+''''
    

    to this? :

    set @sql = @sql + ' and form_name =' + @form_name
    

    I realise you might not want to add any driver specific code to your program, but in procedures like this one, where you have a LIKE statement populated from a variable, I don't know many database drivers that quote the special search characters in the same way, and there is no PDO function to do it.  So if you want to search correctly for names containing an underscore or percent sign, on SQL Server _,%, and [ require escaping; it can be done like this: preg_replace('/([_%\[])/', '[\1]', $str); 


    Rob
    Wednesday, May 4, 2011 2:09 PM
  • p.s. This is how the second statement of the stored proc should work:

    set @sql = @sql + ' and form_name_display like ''%'' + ' + @form_name_display + ' + ''%'' '
    
    

    Rob
    Wednesday, May 4, 2011 5:34 PM
  • Bruce-

    I was able to get your script/sproc working with the following changes...

    In your PHP code where you bind parameters, change your foreach loop so it looks like this (notice I've commented out the call to quote):


    foreach($paramArray as $key=>$value) {
        //$newValue = $objDb->quote($value);
        $procInit->bindParam(":".$key,$paramArray[$key]);
       }


    In your stored procedure, change your logic so that you are not using dynamic SQL. I think this replicates your logic:

    IF
     @mode = 'V'
    BEGIN
    IF (@form_name != '' AND @form_name_display != '')
       BEGIN
          SELECT form_id, form_name, form_name_display, form_comments
          FROM tbl_forms WHERE form_name = @form_name AND form_name_display LIKE '%' + @form_name_display + '%'
       END
    ELSE IF (@form_name != '')
       BEGIN
          SELECT form_id, form_name, form_name_display, form_comments
          FROM tbl_forms WHERE form_name = @form_name
       END
    ELSE IF (@form_name_display != '')
       BEGIN
          SELECT form_id, form_name, form_name_display, form_comments
          FROM tbl_forms WHERE form_name_display LIKE '%' + @form_name_display + '%'
       END
    END

     

    If those changes work, I think I can explain what was happening.
    -Brian

    Edit: I just realized that I'm not answering your question about the PDO::quote method with my suggestion above. The short answer is that if you are using parameterized queries (as you are) there is no need to use the quote method - you don't gain anything by using it and the combination of using parameterized queries, the quote method, and dynamic SQL can get tricky. If you *must* use all three, I'd try Robert's suggestion for modifying your dynamic SQL.


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    Wednesday, May 4, 2011 8:15 PM
  • This reply is to both Brian and Robert:

    Thank you for your suggestions.  I'm eager to try them, however, we lost a bit of time in this project trying to resolve these driver-related issues.  We need to catch up on some key deliverables before we can go back and try these solutions.  Sorry to not be able to give you immediate feedback on what worked and what didn't.

    -Bruce


    Bruce Amick at bbvany dot com
    Friday, May 6, 2011 10:15 PM
  • Bruce-

    Your issue inspired a blog post: http://blogs.msdn.com/b/brian_swan/archive/2011/05/10/using-pdo-quote-with-parameterized-queries.aspx.

    -Brian


    This posting is provided "AS IS" with no warranties, and confers no rights. http://blogs.msdn.com/brian_swan
    Tuesday, May 10, 2011 7:42 PM