quote_smart GOTCHA

If you've ever used PHP with MySQL you've probably used the quote_smart function found in the comments of PHP.net.

	function quote_smart($value)
	{
		if (get_magic_quotes_gpc()) $value = stripslashes($value);
		if($value == '') $value = '';
		else if (!is_numeric($value) || $value[0] == '0') $value = "'" . mysql_real_escape_string($value) . "'"; //Quote if not integer
		return $value;
	}

Code (markup):

I found a very interesting gotcha today. PHP supports very large numbers.

So let's say you have a string "123123123123" and you pass it into quote_smart.

It won't quote it. And when your query goes into MySQL it's now trying to do a numeric comparison with a very very large number. If your table has millions of rows and you're trying to select rows based on the value with that number it will take forever.

The fix?

	function quote_smart($value)
	{
		if (get_magic_quotes_gpc()) $value = stripslashes($value);
		if($value == '') $value = '';
		else if ([b]strlen($value) > 9[/b] || !is_numeric($value) || $value[0] == '0') $value = "'" . mysql_real_escape_string($value) . "'"; //Quote if not integer
		return $value;
	}

Code (markup):

Any number with more than 9 digits has quotes around it so MySQL will treat it like a string. The result is a huge speed boost. Anything 9 digits and less falls into 32 bit numbers and MySQL has no trouble with them.

It may be better to just tell quote_smart if it's a string or number. But I think MySQL can handle conversions fine depending on what type the column is as long as numbers are 32bit or have quotes around them.