Jump to content

Passing NULL using mysql_real_escape_string?


jugdish114

Recommended Posts

I'm trying to pass a NULL to a stored procedure in a PHP script to MySQL, and I am scrubbing the variables using mysql_real_escape_string, but the NULLs that are passed are giving me problems.  My script should look like this:

 

call spMyStoredProc(1,2,NULL,'username');

 

but unfortunately I get:

 

call spMyStoredProc(1,2,,'username');

 

I am wondering if I want to move away from using mysql_real_escape_string, and possibly write my own function?

 

Anybody have any ideas?

 

thanks,

 

Frank C

You have to handle NULL's in a special way. The proper SQL syntax would be:

call spMyStoredProc(1,2,NULL,'username')
to produce that from PHP, you have to test the parameter:

 

$param3 = null;

if ($param3 === null) { // That's a PHP null
  $sql = "call spMyStoredProc(1,2,NULL,'username')";
} else {
  $sql = "call spMyStoredProc(1,2,'" . mysql_real_escape_string($param3) . "','username')";
}
I have a function for escaping that handles this:

function quoteStringNull($pmValue, $psDelimiter = "'") {
  if ($pmValue === null) return 'NULL';
  return $psDelimiter . mysql_real_escaspe_string($pmValue) . $psDelimiter;
}

$param3 = null;
$sql = sprintf('call spMyStoredProc(1,2,%s,"username")', quoteStringNull($param3));
// call spMyStoredProc(1,2,NULL,"username")

$param3 = 'Master';
$sql = sprintf('call spMyStoredProc(1,2,%s,"username")', quoteStringNull($param3));
// call spMyStoredProc(1,2,'Master',"username")
(That's off the top of my head, I don't have access to my library right now).

 

The function returns the string NULL which is sent to mySql properly; or it returns the delimited string value (including the quotes). So the function results can be dropped straight into the sql string.

Thanks DavidAM.  That was the same basic approach I was working on last night; I think the reason why my code wasn't working the way I wanted was that i was saying "if $string == NULL" where your code uses the lowercase null instead.  (Still not sure what the difference is, I need to do some reading)  I will work this new code in tonight, thanks again for the help.

 

fc

This works for my needs:

 

       function null_intval($int_string) {
       
           if ($int_string == "") return 'NULL';
           if ($int_string == null) return 'NULL';
           return mysql_real_escape_string($int_string);
 
       }
 
Thanks again,
 
fc

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.