MySQL database class
Developers should always be vigil for XSS and SQL injection attacks. Data from POST and GET should never be trusted. It is trivial to submit arbitrary values to a web page. When it comes to database insertion, strings should always be purged of unsafe entities. PHP’s MySQL extension offers a good solution.
Once a link identifier ($link) to a database is opened, PHP strings can be cleaned up using
$safe_string = mysql_real_escape_string($unsafe_string, $link);
This is simple and convenient. Below is a similar function to get a feel for what mysql_real_escape_string() is doing.
function escape($str)
{
$search=array("\\","\0","\n","\r","\x1a","'",'"');
$replace=array("\\\\","\\0","\\n","\\r","\Z","\'",'\"');
return str_replace($search,$replace,$str);
}
source: http://php.net/manual/en/function.mysql-escape-string.php
I have written a wrapper class for PHP’s mysql functions that can help with escaping data using mysql_real_escape_string() for safe database insertions. Additionally, it generally reduces the clutter that PHP’s built in query functions can cause.
The basic idea is that the class can be instantiated with
$query = new MySqlDB($hostname, $database, $username, $password);
And then queries can be executed and an array containing the results can be easily captured like so
$query->executeQuery('SELECT * FROM `Table` WHERE `Field1` = %s AND `Field2` = %d', 'string', 12);
while ($row = $query->fetchArray())
{
$field3 = $row['Field3'];
// do something amazing
}
The query parameter is treated as a format string of sorts where %s is cast as a string and %d is cast as an integer from the provided list of arguments. A query string and arguments can also be provided directly into the fetchArray() call for a single line return of the first row. There are also methods to return a single value returned by the query and one to return all rows in a single array.
Some other cool things you can do
// Inserts a row into 'Table' where Field1 = 'string' and Field2 = 12
$insert = array('Field1' => 'string', 'Field2' => 12);
$query->insertAssoc('Table', $insert);
// Updates row where Field3 = 10
$update = array('Field1 => 'string', 'Field2' => 12);
$query->updateAssoc('Table', $update, 'Field3 = %d', 10);