Passing NULL using mysql_real_escape_string?

[jugdish114]

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

[AbraCadaver]

How are you passing NULL?  As PHP null or as 'NULL' as a string?

[jugdish114]

It's a NULL, not a string (eg 'NULL').  I'm not sure if passing null would work where NULL did not.  Does that make sense?

[AbraCadaver]

Sorry, I wasn't paying attention that this was a mysql forum and not PHP.  I was asking to show how you were passing it.

[DavidAM]

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.

[jugdish114]

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

[jugdish114]

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