Is there a PHP library that performs MySQL Data Validation and Sanitization According to Column Type?

Question

Do you know of any open source library or framework that can perform some basic validation and escaping functionality for a MySQL Db.

i envisage something along the lines of:

//give it something to perform the quote() quoteInto() methods
$lib->setSanitizor($MyZend_DBAdaptor); 

//tell it structure of the table - colnames/coltypes/ etc
$lib->setTableDescription($tableDescArray); 

//use it to validate and escape according to coltype 
foreach ($prospectiveData as $colName => $rawValue)
if ( $lib->isValid($colName, $rawValue))
 {
 //add it to the set clause
 $setValuesArray[$lib->escapeIdentifier($colName)] = $lib->getEscapedValue($colName,$rawValue);
 }
else {
 throw new Exception($lib->getErrorMessage());
 }

etc...

I have looked into - Zend_Db_Table (which knows about a table's description), and - Zend_Db_Adaptor (which knows how to escape/sanitize values depending on TYPE)

but although they can sanitize, they do not automatically do any clever validation stuff before updates/inserts

Anyone know of a good PHP library to preform this kind of validation that I could use rather than writing my own?

i envisage alot of this kind of stuff:

   ...  
   elseif (eregi('^INT|^INTEGER',$dataset_element_arr[col_type]))
    {
    $datatype='int';

    if (eregi('unsigned',$dataset_element_arr[col_type]))
        {
        $int_max_val=4294967296;
        $int_min_val=0;
        }
    else    {
        $int_max_val=2147483647;
        $int_min_val=-2147483648;
        }
    }

(p.s I know eregi is deprecated - its just an example of laborious code)

Answer 1

I wrote a lot of the code in Zend_Db.

The code doesn't do a lot of sanitizing or escaping in update/insert because it uses query parameters. You don't need to worry about SQL injection if you pass dynamic values to the query using parameters.

See my answer to avoiding MySQL injections with the Zend_Db class for more details.

MySQL also supports some datatypes like BIGINT that are larger than PHP integers, so you have to represent them in PHP strings instead. Then there are MySQL DATE values and so on. It's better to just validate the values by inserting them into the database and coping with any exceptions.