/**
* Prepares a SQL query for safe execution. Uses sprintf()-like syntax.
*
* @version 1.0
* @since 1.0
*
* @param array $args | Query args array
* => VAL @param string [0] | First key in array is query string in vsprintf() format
* => VAL @param mixed [N] | Each successive key is a var referred to in the query string
*
* @return string | Prepared query string
*/
function prepare($query, $params = null)
{
// Force floats to be locale unaware
$query = preg_replace('|(?<!%)%f|', '%F', $query);
// Quote the strings, avoiding escaped strings like %%s
$query = preg_replace('|(?<!%)%s|', "'%s'", $query);
// Replace our %r raw string token with an unquoted %s
$query = preg_replace('|(?<!%)%r|', "%s", $query);
$escaped_params = array();
if ($params) {
$cast = new FOX_cast();
foreach ($params as $param) {
if (!FOX_sUtil::keyExists('escape', $param) || !FOX_sUtil::keyExists('val', $param) || !FOX_sUtil::keyExists('php', $param) || !FOX_sUtil::keyExists('sql', $param)) {
$text = "SAFETY INTERLOCK TRIP [ANTI SQL-INJECTION] - All data objects passed to the ";
$text .= "database driver must include 'val', 'escape', 'php', and 'sql' parameters. This ";
$text .= "interlock cannot be disabled.";
throw new FOX_exception(array('numeric' => 1, 'text' => $text, 'data' => $param, 'file' => __FILE__, 'class' => __CLASS__, 'function' => __FUNCTION__, 'line' => __LINE__, 'child' => null));
}
try {
$cast_val = $cast->PHPToSQL($param['val'], $param['php'], $param['sql']);
} catch (FOX_exception $child) {
throw new FOX_exception(array('numeric' => 2, 'text' => "Error while casting parameter", 'data' => array("val" => $param['val'], "php" => $param['php'], "sql" => $param['sql']), 'file' => __FILE__, 'class' => __CLASS__, 'function' => __FUNCTION__, 'line' => __LINE__, 'child' => $child));
}
if ($param['escape'] !== false) {
// NOTE: parameters are in reverse order from mysqli_real_escape_string()
$escaped_params[] = mysql_real_escape_string($cast_val, $this->dbh);
} else {
$escaped_params[] = $cast_val;
}
}
unset($param);
}
$result = vsprintf($query, $escaped_params);
return $result;
}
/**
* Builds an indate [INsert-upDATE] query for processing by $wpdb->prepare().
*
* @version 1.0
* @since 1.0
*
* @param array $struct | Structure of the db table, @see class FOX_db header for examples
*
* @param array/object $data | Class with $column_1, $column_2 in the namespace, or array of the form ("column_1"=>"value_1", "column_2"=>"value_2")
* => KEY @param string | Name of the db column this key describes
* => VAL @param int/string | Value to assign to the column
*
* @param bool/array $columns | Columns to use in query. NULL to select all columns.
* => VAL @param string $mode | Column operating mode. "include" | "exclude"
* => VAL @param string/array $col | Single column name as string. Multiple column names as array of strings
*
* @return array | Exception on failure. Query array on success.
*/
public function buildIndateQuery($struct, $data, $columns = null)
{
// Switch between unit test mode (pass as array) and
// normal mode (pass as class name)
// ====================================================
if (is_string($struct)) {
$struct = call_user_func(array($struct, '_struct'));
}
// ====================================================
$params_list = array();
$columns_list = array();
$columns_list = array_keys($struct["columns"]);
// Handle data passed as array where the array has missing
// or nonexistent db column names
// =====================================================
if (is_array($data)) {
$columns_list = array_intersect($columns_list, array_keys($data));
}
// Include or exclude one or more columns from the query
// ######################################################
if ($columns != null) {
if (!is_array($columns["col"])) {
// Handle single column name as string
$temp = array();
$temp[0] = $columns["col"];
$columns["col"] = $temp;
}
if ($columns["mode"] == "include") {
$column_names = array_intersect($columns_list, $columns["col"]);
} elseif ($columns["mode"] == "exclude") {
$column_names = array_diff($columns_list, $columns["col"]);
}
} else {
$column_names = $columns_list;
}
// Build the INSERT columns string
// ######################################################
$columns_count = count($column_names) - 1;
$columns_left = $columns_count;
foreach ($column_names as $column_name) {
$insert_columns_string .= $column_name;
if ($columns_left != 0) {
$insert_columns_string .= ", ";
$columns_left--;
}
}
// CASE 1 - Array Mode
// ==============================
if (is_array($data)) {
$cast = new FOX_cast();
$query_formats .= "(";
$columns_left = $columns_count;
foreach ($column_names as $column_name) {
$query_formats .= $struct["columns"][$column_name]["format"];
$in_type = $struct["columns"][$column_name]["php"];
$out_type = $struct["columns"][$column_name]["sql"];
if ($struct["columns"][$column_name]["format"] == "%r") {
$escape = false;
} else {
$escape = true;
}
$params_list[] = array('escape' => $escape, 'val' => $cast->PHPToSQL($data[$column_name], $in_type, $out_type));
if ($columns_left != 0) {
$query_formats .= ", ";
$columns_left--;
}
}
$query_formats .= ")";
} else {
$columns_left = $columns_count;
$cast = new FOX_cast();
$query_formats .= "(";
foreach ($column_names as $column_name) {
$query_formats .= $struct["columns"][$column_name]["format"];
$in_type = $struct["columns"][$column_name]["php"];
$out_type = $struct["columns"][$column_name]["sql"];
if ($struct["columns"][$column_name]["format"] == "%r") {
$escape = false;
} else {
$escape = true;
}
$params_list[] = array('escape' => $escape, 'val' => $cast->PHPToSQL($data->{$column_name}, $in_type, $out_type));
if ($columns_left != 0) {
$query_formats .= ", ";
$columns_left--;
}
}
$query_formats .= ")";
}
// Build the UPDATE columns string
// ######################################################
$columns_left = $columns_count;
foreach ($column_names as $column_name) {
$update_columns_string .= $column_name . " = " . $struct["columns"][$column_name]["format"];
$in_type = $struct["columns"][$column_name]["php"];
$out_type = $struct["columns"][$column_name]["sql"];
$cast = new FOX_cast();
if ($struct["columns"][$column_name]["format"] == "%r") {
$escape = false;
} else {
$escape = true;
}
if (is_array($data)) {
// Handle data passed as array
$params_list[] = array('escape' => $escape, 'val' => $cast->PHPToSQL($data[$column_name], $in_type, $out_type));
} else {
// Handle data passed as object
$params_list[] = array('escape' => $escape, 'val' => $cast->PHPToSQL($data->{$column_name}, $in_type, $out_type));
}
if ($columns_left != 0) {
$update_columns_string .= ", ";
$columns_left--;
}
}
$query = "INSERT INTO " . $this->base_prefix . $struct["table"] . " (" . $insert_columns_string . ") VALUES " . $query_formats . " ON DUPLICATE KEY UPDATE " . $update_columns_string;
// Merge all return data into an array
// ###############################################################
$result = array('query' => $query, 'params' => $params_list);
return $result;
}
