On 5/26/06, Satyam <Satyam@xxxxxxxxxxxxx> wrote:> ----- 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;> }>>
Thanks, Satyam. I'm having a hard time following your code, however.What is the advantage of it? I'm not criticizing, rather, trying tolearn. What types of attacks will it prevent that the code I copiedfrom the manual will not?
Thanks.
Dotan Cohenhttp://gmail-com.com
