Doctrine 1
  1. Doctrine 1
  2. DC-841

Doctrine_Connection_Mssql::replaceBoundParamsWithInlineValuesInQuery regex failing to replace all '?' instances [patch+]

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 1.2.3
    • Fix Version/s: None
    • Component/s: Connection
    • Labels:
      None
    • Environment:
      PHP 5.2.11, Apache, Microsoft SQL Server 2005

      Description

      When executing queries with WHERE statements using multiple instances of the "<>" operator (as well as other non =,( symbols inbetween definitions), the method Doctrine_Connection_Mssql::replaceBoundParamsWithInlineValuesInQuery fails to identify all ? replacements.

      In the following piece of code I have a query (trimmed for readability and renamed for privacy) that fails to have all "?" symbols replaced as well as the relevant code from the method mentioned above (minus the return statement) doing a simple demonstration:

      <?php 
      $query = "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> ? AND [t].[field2] <> ? AND [t].[field2] LIKE ?)";
      
      $params = array(
      	"'param1'",
      	"'param2'",
      	"'param3'"
      );
      
      
      /**
       * Replaces bound parameters and their placeholders with explicit values.
       *
       * Workaround for http://bugs.php.net/36561
       *
       * @param string $query
       * @param array $params
       */
      //protected function replaceBoundParamsWithInlineValuesInQuery($query, array $params) {
      
      	foreach($params as $key => $value) {
      		if(is_null($value)) {
      			$value = 'NULL';
      		}
      		else {
      			//$value = $this->quote($value); //REMOVED AS PRE-ADDED QUOTES TO ABOVE PARAMETER LIST
      		}
      
      		$re = '/([=,\(][^\\\']*)(\?)/iU';
      		
      		$matches = array();
      		preg_match($re,$query,$matches);
      		
      		var_dump($matches); //ADDED FOR DEMONSTRATION
      
      		$query = preg_replace($re, "\\1 {$value}", $query, 1);
      		
      		var_dump($query); //ADDED FOR DEMONSTRATION
      	}
      
      //	return $query;
      //
      //}

      Running this code produces:

      array(3) {
        [0]=>
        string(18) "([t].[field1] <> ?"
        [1]=>
        string(17) "([t].[field1] <> "
        [2]=>
        string(1) "?"
      }
      string(108) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <>  'param1' AND [t].[field2] <> ? AND [t].[field2] LIKE ?)"
      array(0) {
      }
      string(108) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <>  'param1' AND [t].[field2] <> ? AND [t].[field2] LIKE ?)"
      array(0) {
      }
      string(108) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <>  'param1' AND [t].[field2] <> ? AND [t].[field2] LIKE ?)"

      Unfortunately the regex will not identify all the ? instances properly in the query when run like preg_match_all(), which was my first idea to fix (pre-identify all ? instances, then go through and replace them).

      The only 3 potential solutions I can think of are:

      1. Pre-identify all ?'s and note their position in the string, to do this using a much looser regex, then replace all the ?'s found
      2. Use a stack a loop and a switch statement to scan for eligible ? replacements. I have some sample code that shows this should work, but performance is not something I have tested.
      3. Revert back to the previous behavior. Scanning the changelogs I really don't know where this broke, other than queries that worked prior to 1.2.3 have since broken and this method is the cause.

        Activity

        Hide
        Daniel Cousineau added a comment - - edited

        I am probably way over thinking a solutions, however since I have to run home and don't have time to flesh this out further at the moment, my initial idea is something like this:

        <?php 
        $query = "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> ? AND [t].[field?] LIKE ? AND [t].[field3] = ?)";
        
        $params = array(
        	"'param1'",
        	"param2?",
        	"'param3'"
        );
        
        var_dump($query);
        
        $stack = array();
        $stringDelim = array("'", '"');
        
        $i = 0;
        foreach( str_split($query) as $char )
        {
        	switch($char) {
        		
        		case "[":
        			if( !in_array(end($stack), $stringDelim) )
        				array_push($stack, $char);
        			
        			break;
        			
        		case "]":
        			if( end($stack) == "[" ) {
        				array_pop($stack);
        			} else
        				array_push($stack, $char);
        				
        			break;
        		
        		case "\"":
        		case "'":
        			if( end($stack) == $char )
        				array_pop($stack);
        			else
        				array_push($stack, $char);
        			
        			break;
        		
        		case "?":
        			if( !in_array(end($stack), array_merge($stringDelim, array("[", "]"))) )
        			{
        				$param = array_shift($params);
        				$query = substr_replace($query, $param, $i, 1);
        				
        				$i += strlen($param) - 1;
        				var_dump($query);
        			}
        			
        			
        			break;
        		
        		default:
        	}
        	
        	$i++;
        }
        
        var_dump($query);
        

        Which produces

        string(131) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> ? AND [t].[field?] LIKE ? AND [t].[field3] = ?)"
        string(138) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> 'param1' AND [t].[field?] LIKE ? AND [t].[field3] = ?)"
        string(144) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> 'param1' AND [t].[field?] LIKE param2? AND [t].[field3] = ?)"
        string(151) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> 'param1' AND [t].[field?] LIKE param2? AND [t].[field3] = 'param3')"
        string(151) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> 'param1' AND [t].[field?] LIKE param2? AND [t].[field3] = 'param3')"

        Which is what we would expect, as well as it doesn't take into account for newly inserted data containing '?' symbols.

        But again this is just playing around with potential solutions.

        Show
        Daniel Cousineau added a comment - - edited I am probably way over thinking a solutions, however since I have to run home and don't have time to flesh this out further at the moment, my initial idea is something like this: <?php $query = "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> ? AND [t].[field?] LIKE ? AND [t].[field3] = ?)"; $params = array( "'param1'", "param2?", "'param3'" ); var_dump($query); $stack = array(); $stringDelim = array("'", '"'); $i = 0; foreach( str_split($query) as $char ) { switch($char) { case "[": if( !in_array(end($stack), $stringDelim) ) array_push($stack, $char); break; case "]": if( end($stack) == "[" ) { array_pop($stack); } else array_push($stack, $char); break; case "\"": case "'": if( end($stack) == $char ) array_pop($stack); else array_push($stack, $char); break; case "?": if( !in_array(end($stack), array_merge($stringDelim, array("[", "]"))) ) { $param = array_shift($params); $query = substr_replace($query, $param, $i, 1); $i += strlen($param) - 1; var_dump($query); } break; default: } $i++; } var_dump($query); Which produces string(131) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> ? AND [t].[field?] LIKE ? AND [t].[field3] = ?)" string(138) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> 'param1' AND [t].[field?] LIKE ? AND [t].[field3] = ?)" string(144) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> 'param1' AND [t].[field?] LIKE param2? AND [t].[field3] = ?)" string(151) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> 'param1' AND [t].[field?] LIKE param2? AND [t].[field3] = 'param3')" string(151) "SELECT * FROM [table] AS [t] WHERE ([t].[field1] <> 'Testing!?' AND [t].[field2] <> 'param1' AND [t].[field?] LIKE param2? AND [t].[field3] = 'param3')" Which is what we would expect, as well as it doesn't take into account for newly inserted data containing '?' symbols. But again this is just playing around with potential solutions.
        Hide
        Enrico Stahn added a comment -

        The patch for DC-545 broke some queries or didn't worked at all. Unfortunately there was no Test Case for DC-545.

        The patch and a Test Case can be found at: http://github.com/estahn/doctrine1/compare/master...DC-841

        Show
        Enrico Stahn added a comment - The patch for DC-545 broke some queries or didn't worked at all. Unfortunately there was no Test Case for DC-545 . The patch and a Test Case can be found at: http://github.com/estahn/doctrine1/compare/master...DC-841
        Hide
        Enrico Stahn added a comment -

        I made a mistake with github, the updated branch can be found at
        http://github.com/estahn/doctrine1/tree/DC-841-2

        Show
        Enrico Stahn added a comment - I made a mistake with github, the updated branch can be found at http://github.com/estahn/doctrine1/tree/DC-841-2
        Hide
        Lionel ROTA added a comment -

        Doesn't work with :

        'Test' <> 'Test !?'
        

        The question mark is captured...

        This code seems working :

        foreach($params as $key => $value) {
          if(is_null($value)) {
            $value = 'NULL';
          }
          else {
            $value = $this->quote($value);
          }
        
          $re = '/((?:[=<>,\(]|LIKE|IS)[^\\\']*)(\?)/iuU';
        
          $query = preg_replace($re, "\\1 {$value}", $query, 1);
        }
        
        Show
        Lionel ROTA added a comment - Doesn't work with : 'Test' <> 'Test !?' The question mark is captured... This code seems working : foreach($params as $key => $value) { if (is_null($value)) { $value = 'NULL'; } else { $value = $ this ->quote($value); } $re = '/((?:[=<>,\(]|LIKE|IS)[^\\\']*)(\?)/iuU'; $query = preg_replace($re, "\\1 {$value}" , $query, 1); }

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Daniel Cousineau
          • Votes:
            3 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated: