newbie help - \ seems to be converting to '

[mrblah]

Hi all, new to this board and php and I've ran into a problem with an inventory management system I'm supporting. The problem is I have a table in MS SQL Server 2000 that contains serial numbers for parts and the serial numbers can contain single quotes and backslashes. I have it correctly accepting single quotes , however when I have a serial number with a backslash, either the query doesn't return anything (because it's looking for \\) or when I do a REPLACE() and replace all occurances of \\ with \, it throws a sql error - posted below is my code and the query before it is executed and after it is executed per print() statements.

 

  $query = "SELECT Part_serial_id 
				FROM PART_SERIAL 
				WHERE part_number = '$part' 
					and Serial_number = REPLACE('$serial_number','\\\\','\\')
					and base_id = $GLOBALS[uSERBASEID]";
          print("query is ".$query);
         $con->query($query);

 

$con->query() is defined below:

  Function query ($query_string)
  	  {
           $query_string = strtr($query_string, "\'" , "''" );     //SQL DB fix
           #die($query_string);
  	   $this->result_id = mssql_query ($query_string, $this->connection_id) ;
  	   If ($this->result_id == false)
  		{
  		   $this->Disp_Error_Page ("SQL Error: $query_string") ;
  		}
  	   Return $this->result_id ;
  	}

In the code above, what I'm trying to do is replace all occurances of \\ with a single \

 

Here's my debug output using print() statements:

 

query is SELECT Part_serial_id

FROM PART_SERIAL

WHERE part_number = 'partp'

and Serial_number = REPLACE('1''''2\\3''''5','\\','\')

and base_id = 1

SQL Error: SQL Error: SELECT Part_serial_id

FROM PART_SERIAL

WHERE part_number = 'partp'

and Serial_number = REPLACE('1''''2''3''''5','''',''')

and base_id = 1 Line 4: Incorrect syntax near '')

and base_id = 1'.

 

I know why it's causing the error (too many '') but I don't understand why the \\ are being replaced with ''''. Note the first query runs fine in query analyzer.

Thanks in advance for any help for the php newbie

[ballhogjoni]

You need to escape all the quotes.

 

REPLACE('1''''2\\3''''5','\\','\')

 

too

 

REPLACE('1\'\'\'\'2\\3\'\'\'\'5',\'\\\',\'\\')

 

Unless you want the quotes then you will have to do something like

 

REPLACE("'1''2\\'3''5','\\','\\'")

 

Try that and see what happens.

 

[mrblah]

Unfortunately, I need the quotes since the serial number is stored as : 1''2\3''5 in the database which to the user is 1'2\3'5.

I can't escape the quotes either, because in a file called sql.inc.php in which the database connections and functions are defined (I'm not able to modify this file), the function query($query_string) replaces all occurances of \' with '', leaving me with a mess of quotes and of course no search results (provided that it didn't error out). Hope this makes sense.

 

thanks for your time

 

[ballhogjoni]

sounds like this $query_string = strtr($query_string, "\'" , "''" ); needs to be changed too

 

$query_string = strtr($query_string, "\'" , "'" );