jugdish114 Posted October 4, 2013 Share Posted October 4, 2013 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 Link to comment https://forums.phpfreaks.com/topic/282716-passing-null-using-mysql_real_escape_string/ Share on other sites More sharing options...
AbraCadaver Posted October 4, 2013 Share Posted October 4, 2013 How are you passing NULL? As PHP null or as 'NULL' as a string? Link to comment https://forums.phpfreaks.com/topic/282716-passing-null-using-mysql_real_escape_string/#findComment-1452591 Share on other sites More sharing options...
jugdish114 Posted October 4, 2013 Author Share Posted October 4, 2013 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? Link to comment https://forums.phpfreaks.com/topic/282716-passing-null-using-mysql_real_escape_string/#findComment-1452595 Share on other sites More sharing options...
AbraCadaver Posted October 4, 2013 Share Posted October 4, 2013 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. Link to comment https://forums.phpfreaks.com/topic/282716-passing-null-using-mysql_real_escape_string/#findComment-1452601 Share on other sites More sharing options...
DavidAM Posted October 4, 2013 Share Posted October 4, 2013 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. Link to comment https://forums.phpfreaks.com/topic/282716-passing-null-using-mysql_real_escape_string/#findComment-1452602 Share on other sites More sharing options...
jugdish114 Posted October 4, 2013 Author Share Posted October 4, 2013 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 Link to comment https://forums.phpfreaks.com/topic/282716-passing-null-using-mysql_real_escape_string/#findComment-1452605 Share on other sites More sharing options...
jugdish114 Posted October 4, 2013 Author Share Posted October 4, 2013 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 Link to comment https://forums.phpfreaks.com/topic/282716-passing-null-using-mysql_real_escape_string/#findComment-1452613 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.