----- Original Message -----
From: "Dotan Cohen" <dotancohen@xxxxxxxxx>
> // Make a safe query
> $query = sprintf("SELECT * FROM users WHERE user=%s AND password=%s",
> quote_smart($_POST['username']),
> quote_smart($_POST['password']));
>
> mysql_query($query);
> ?>
>
> What is the purpose of the sprintf?
Can't see any reason for that.
I don't know if I already posted this on this list or perhaps in the
Spanish
one, but below I'm copying my SQL building function, like an sprintf but
for
SQL.
Notice it assumes a global variable $table_prefix that is meant for
customized table prefixes, to avoid conflicts with existing applications.
Depending on the font and line length, it will be broken in many parts.
The
arrows showing the different parts of the regular expresion point
anywhere,
but should be fine if shown in a fixed pitch font.
Satyam
/**
* Builds a properly formatted and escaped SQL statement using an SQL
template and a list of arguments.
*
* The function scans the template for query marks ? which are
placeholders
for the arguments
* Query marks are to be followed by format descriptors.
*
* The first argument, the template, is mandatory. If the template
contains no query marks
* and no argument is given, the function does nothing.
*
* Placeholders have the following format and are not case sensitive:
*
* <b>?[nn][m]t</b>
*
* Where:
*
* <b>?</b> Begining of placeholder for argument
*
* <b>nn - <i>position</i></b> number of the argument to be replaced.
* Argument 0 is the template itself and is not valid.
* The first argument after the SQL template is number 1
* If no number is given, arguments are taken sequentially.
* Numbered replacements do not move the sequential argument pointer.
* Arguments beyond the actual number present are considered null
*
* <b>m - <i>modifier</i></b> [optional] indicates what to do if the
argument is null
* - m: mandatory, if the argument contains null, it will give a fatal
error.
* - z: null, if the argument is 0 or an empty string, it will be
replaced
by null
*
* <b>t - <i>data type</i></b> the placeholder will be replaced by the
argument as follows
* - s: string, if not null, it will be escaped and enclosed in quotes
* - i: integer, the integer value (intval() function) of the argument
* - f: float, the floating point value (floatval() function) of the
argument
* - d: date, the argument will be assumed to represent a timestamp and
it
will be converted to yyyy-mm-dd and quoted
* - b: boolean, anything evaluated to false will be 0, otherwise 1
* - t: table prefix, the value of the global variable
<i>$table_prefix</i>, escaped and unquoted
* It takes no argument from the argument list
*
* Example:
* <code>
* echo BuildSql('Insert into ?ttable
(?s,?ns,?mi,?d,?ni,?i)','Something','',5,time(),0,null);
* </code>
* will return:
* <pre>
* Insert into wp_table ('Something',null,5,'2006-05-15',null,0)
* </pre>
*
* Note that placeholders do not need to be quoted, if quotes are
required
(strings or dates) they will be provided
*
* @param string $query Template of SQL statement
*
*
* @param mixed $value,... Values to be replaced into placeholders,
sequentially unless stated otherwise
*
* @return string properly formated and escaped SQL statement
*
* The function will trigger a fatal error if an unknown formatting
character is found.
* Unused arguments will produce warnings.
* Missing arguments will be assumed null and will trigger a fatal error
* if the placeholder has the mandatory modifier m.
* There is no provision to put a literal ? into the SQL statement since
the ? is not a valid SQL operator,
* the only valid place for query marks are in literal string
constants,
which can be passed to this
* function in an argument
*/
$table_prefix = 'wp_';
echo BuildSql('Insert into ?ttable
(?s,?ns,?mi,?d,?ni,?i)','Something','',5,time(),0,null);
function BuildSql($query) {
global $table_prefix;
$num_args = func_num_args(); // number of arguments available
$args_used = (1 << $num_args) -2; // bit mask to check if arguments are
used
/* +---------Anything up to first query mark
| +------ query mark, start of
placeholder
| | +---- position of argument
| | | +----- modifier
| | | | +--- data type
| | | | | */
if
(preg_match_all('|([^\?]*)(\?(\d?\d)?([mn]?)([sifdbt])?)*|i',$query,$matches,PREG_SET_ORDER))
{
$arg_pointer = 1; // sequential pointer to arguments
$s = ''; // output SQL statement
foreach($matches as $match) {
$NullIfEmpty = false;
$s .= $match[1]; //concatenate everything up to question mark
$type = strtolower($match[5]); // read datatype
// read the value of the argument
if ($type =='t') {
$value = $table_prefix; // t is a special case, it takes no
argument
from the list
$n = 0;
} elseif (intval($match[3])) { // if there is a nn position modifier,
read it
// (null evaluates to 0, which is an invalid position anyhow)
$n = intval($match[3]);
} else { // else, read the next sequential argument an increment
the
argument pointer
$n = $arg_pointer++;
}
if ($n) { // if there was an argument to be read (notice t does not
ask
for an argument)
$args_used &= ~ (1 << $n); // some bitwise magic to unset the bit
position representing the argument
if ($n >= $num_args) $value = null; // if argument requeste beyond
those available, assume it null
else $value = func_get_arg($n); // otherwise, read it
}
switch ($match[4]) { // read modifier
case 'm': // mandatory?
case 'M':
if (is_null($value)) trigger_error("Missing value for argument $n
near:
... ${match[0]} ...",E_USER_ERROR);
break;
case 'n': // null if empty?
case 'N':
$NullIfEmpty = true; // set flag
break;
}
switch($type) { // now we process $value according to datatype and
$NullIfEmpty flag
case 's':
case 'S':
if ($NullIfEmpty and strlen($value) == 0) {
$s .='null';
} else {
$s .= "'" . mysql_real_escape_string($value) . "'"; //
replace
with this->escape if within class wpdb
}
break;
case 'i':
case 'I':
if ($NullIfEmpty and $value == 0) {
$s .='null';
} else {
$s .= intval($value);
}
break;
case 'f':
case 'F':
if ($NullIfEmpty and $value == 0) {
$s .='null';
} else {
$s .= floatval($value);
}
break;
case 'd':
case 'D':
if ($NullIfEmpty and empty($value)) {
$s .='null';
} else {
$s .= "'" . date('Y-m-d', $value) . "'";
}
break;
case 'b':
case 'B': // booleans cannot be null
$s .= intval($value!=false);
break;
case 't':
case 'T':
$s .= mysql_real_escape_string($value);
break;
case null:
break;
default:
trigger_error('Unknown formatting character in BuildSql: ' .
$match[0]
. " en SQL <br />[$query]",E_USER_ERROR);
}
}
}
// if all arguments used, $args_used will be zero
if ($args_used) {
for($i=1;$i<$num_args;$i++) {
$args_used >>=1; // bitmaks is shift right
// if right most bit still 1, it means it has not been used.
if ($args_used & 1) trigger_error("Argument $i not used");
}
}
return $s;
}
