Chris Rossi

This is a handy PHP function I wrote to prepare values for insertion into a MySQL database.

For example, if you prefer to use arrays of data and have a function automatically join them together:

$data = array();
$data['field1'] = 'value1';
$data['field2'] = 'value2';
$sql = build_sql_insert('table',$data);
// Returns INSERT INTO `table` (`field1`, `field2`) VALUES ('value1', 'value2');
$sql = build_sql_update('table',27,$data);
// Returns UPDATE `table` SET `field1`='value1', `field2`='value2' WHERE `id` = 27 LIMIT 1;

It can make life much easier as you don’t need to manually write all your SQL queries and list every single field.
It especially helps when you have both an insert and update query in the same page, it saves you from having to add a new field twice:

$table = 'mytable'; // Name of the table
$data = array();
$data['field1'] = $_POST['field1'];
$data['field2'] = $_POST['field2'];

if(isset($_POST['id'])) {

	// An ID was submitted, so this must be an UPDATE
	$sql = build_sql_update($table,$_POST['id'],$data);
}
else {

	// No ID was submitted, so this is an INSERT
	$sql = build_sql_insert($table,$data);
}

$result = $db->query($sql);

But there can be issues with the data in the array itself being “unclean” before database insertion.
Here were the requirements for the function I had to build:

1. It had to detect NULL data types and dates of ’0000-00-00′ and insert them as ‘NULL’
2. It had to trim whitespace from the ends of submitted values
3. If the value is text, it should be hex encoded to prevent single quotes and special characters from breaking the query

With these requirements in mind, here is the function:

function format_value($value) {

	switch(gettype($value)) {

		case "string":

			$value = trim($value);
			if($value == '') {

				$output = "''";
			}
			elseif($value == '0000-00-00' || $value == 'NULL') {

				$output = 'NULL';
			}
			elseif(is_numeric($value) || $value == "NOW()") {

				$output = $value;
			}
			else {
				// Encode as hex when required
				$output = "0x".bin2hex(stripslashes($value))."";
			}
		break;

		case "integer":
			$output = $value;
		break;

		case "NULL":
			$output = 'NULL';
		break;

		default:
			$value = trim($value);
			$output = "'".$value."'";
		break;
	}
	return $output;
}

To break it down, it detects the type of value that was passed (string, integer, null or other), and handles each appropriately. NULL values are submitted into the database as a MySQL NULL data type. Integers are submitted as such, rather than being inserted as strings. Empty values are submitted as blank strings. Dates of ’0000-00-00′ and strings of ‘NULL’ (ie the text is ‘NULL’ but the type is ‘string’) are submitted as the NULL datatype. Strings that are numeric, and strings of ‘NOW()’ are submitted without quotes, so they’re inserted as integers and the current timestamp function respectively. All other strings are submitted as hex encoded values, this avoids the issue of having to use addslashes() etc.

To call this function, it would be used in such a manner:

$table = 'mytable'; // Name of the table
$data = array();
$data['field1'] = format_value($_POST['field1']);
$data['field2'] = format_value($_POST['field2']);

Of course, you can easily add your own MySQL data sanitization code to the above (if you’re concerned about SQL Injection attacks), but this should be a start!

I wrote this code quite a while ago, since then the PHP gettype() function has become deprecated. I’ll most likely replace this code with the PHP is_*() functions, such as is_numeric(), is_string(), is_null() and is_int().