Safe MySQL Parse Function

Wildhoney · 11-30-2007, 12:21 PM

This is the function I use when constructing MySQL statements. It allows you to pass in any argument into the function and it will return it back to you all nice and safe.

As the function automatically quotes strings for you, there's no need to quote them in your statement. So for a string I wouldn't need to add the quotes around %s. It would be like so:

php Code:

sprintf("SELECT * FROM myTable WHERE myColumn = %s", mysql_parse_values($szItem));

The same goes for integers. The function, however, won't add quotes to an integer. The function even checks for the presence of the ever-annoying GPC, and acts accordingly based on its state.

php Code:

function mysql_parse_value($mValue, $bStripTags = true, $bAllowableTags = null)
{
    if (is_array($mValue))
    {
        return; 
    }
    
    if (get_magic_quotes_gpc())
    {
        $mValue = stripslashes($mValue);
    }
         
    if ($bStripTags)
    {
        $mValue = strip_tags($mValue, $bAllowableTags);
    }
        
    if (!is_numeric($mValue))
    {
     $mValue = "'" . mysql_real_escape_string($mValue) . "'";
    }

    return $mValue;
}

Salathe · 11-30-2007, 04:07 PM

I think it's a fair start, but is far from ideal.

Returning null when we have an array - what's that all about?
With the magic quotes and tag stripping - that's usually only applicable for strings but you'll apply it on anything passed into the $mValue argument. Any non-string value will (attempt to) be converted into a string at some point before $mValue is returned - do we want that?
The check for $mValue not being numeric - that obviously means anything which isn't a numeric value (or string representation of a numeric value). Resources, objects, boolean, you name it.

Take for example the following test page:

php Code:

<?php

// Wildhoney's function 
function mysql_parse_value($mValue, $bStripTags = true, $bAllowableTags = null)
{
    if (is_array($mValue))
    {
        return; 
    }
   
    if (get_magic_quotes_gpc())
    {
        $mValue = stripslashes($mValue);
    }
         
    if ($bStripTags)
    {
        $mValue = strip_tags($mValue, $bAllowableTags);
    }
       
    if (!is_numeric($mValue))
    {
     $mValue = "'" . mysql_real_escape_string($mValue) . "'";
    }

    return $mValue;
}


/*
    ==========
    Salathe's testing
    ==========
*/
error_reporting(E_ALL | E_STRICT);
header('Content-Type: text/plain; charset=utf-8');
$tests = array
(
    '(string) "test" ' => 'test',
    '   (int) 12345  ' => 12345,
    ' (float) 0.01   ' => 0.01,
    '   (int) 0      ' => 0,
    '   (hex) 0xFF   ' => 0xFF,
    '(string) "0xFF" ' => "0xFF",
    '  (bool) false  ' => false,
    '  (bool) true   ' => true,
    '   (res) $im    ' => imagecreatetruecolor(10,10),
    ' (array) a,b,c  ' => array('a', 'b', 'c'),
    '(object) a,b,c  ' => (object) array('a', 'b', 'c')
);

foreach ($tests as $label => $test)
{
    echo $label;
    var_dump( mysql_parse_value($test) );
}

Which produces:

Code:

(string) "test" string(6) "'test'"
   (int) 12345  string(5) "12345"
 (float) 0.01   string(4) "0.01"
   (int) 0      string(1) "0"
   (hex) 0xFF   string(3) "255"
(string) "0xFF" string(4) "0xFF"
  (bool) false  string(2) "''"
  (bool) true   string(1) "1"
   (res) $im    string(16) "'Resource id #2'"
 (array) a,b,c  NULL
(object) a,b,c  <br />
<b>Catchable fatal error</b>:  Object of class stdClass could not be converted to string in <b>/www/public_html/talkphp/misc.php</b> on line <b>18</b><br />

Combine those results with actually putting them into a SQL query and you can see why the current function isn't quite ideal yet. For example, asking for myColumn = '' might work if you want false rows when myColumn is BIT/BOOL but shouldn't you query for TRUE/FALSE or at least 1/0?

Wildhoney · 11-30-2007, 11:34 PM

You're quite right, I've not actually tested it so far but I shall be doing some further testing before I implement it into another system.