creation of argument safe database_query without preg callback | PHP

rolf wroteI dont know why youre not using embedded php functions to escape sql and html. As for type checking (date, numbers...) i think they should be replaced with javascript on the client side.

The thing I like about your approach, is the syntax.

Why not replace %d, %s, etc... with %text and %html for example

Well we do agree that

database_query("INSERT INTO TABLE (s1, s2, s3, s4) VALUES(%d, '%s', '%s', %b)", $arg1, $arg2, $arg3, $arg4);

is better than

if(!is_numeric($arg1)){$arg1 = 0;} //well just for the sake of fastness, but i usually i do have a check that returns the form to the user so he would correct the input
$arg2= mysql_real_escape_string($arg2);
$arg3= mysql_real_escape_string($arg3);
$arg4= "'".mysql_real_escape_string($arg4)"'";
mysql_query(sprintf("INSERT INTO TABLE (s1, s2, s3, s4) VALUES(%d, '%s', '%s', %b)", $arg1, $arg2, $arg3, $arg4), $connection);

;) cheezy syntax

now about your question i use the functions provided by mysql(mysql_real_escape_string) because imo it's the most optimized for this, most importantly when i use it to insert values, i can SELECT those values back and mysql sends them to me in their UNESCAPED form ;)

anyway here's a warning for anyone reading this forum maybe as a reference
when using mysql_real_escape string do not write code like this

mysql_query(sprintf("INSERT INTO TABLE (s1, s2, s3, s4) VALUES(%d, `%s`, `%s`, %b)", $arg1, $arg2, $arg3, $arg4), $connection);

but

mysql_query(sprintf("INSERT INTO TABLE (s1, s2, s3, s4) VALUES(%d, '%s', '%s', %b)", $arg1, $arg2, $arg3, $arg4), $connection);

because mysql_real_escape_string doesn't escape the char(`), but it does escape the single quote

okay since you were generous enough to invest some of your time and take a stab at it, here's my newest version of the function

function database_query() {
  $args = func_get_args();
  $original_query = array_shift($args); // remove first array entry
  if (isset($args[0]) and is_array($args[0])) { 
      // Support for 'All arguments in one array' syntax
    $args = $args[0];
  }
  $keys = $original_query;
  $keys = explode('%', $keys);
  array_shift($keys);
  $i = 0; 
  foreach($keys as &$el){
  	$i++; 
    $el = substr((string)$el,0,1)."$i"; // concatenated $i to make unique keys 
  }
  //print_r($keys);

  
  $new_args = array();
    
  if(isset($args) && count($args)!=0){
      $args = array_combine($keys, $args);
      print_r($args);
      
      foreach($args as $key => $value){
 	  $key = substr($key, 0, 1);
      switch ($key) {
        case 'd': 
          $new_args[] = $value; break; 
        case 's':
          // WARNING: i am only using urlencode for testing without database interaction
          // for testing with a database, replace urlencode with mysql_real_escape_string for example
          $new_args[] = urlencode($value); break;
        case 'n':
          // Numeric values have arbitrary precision, so can't be treated as float.    
          $new_args[] = is_numeric($value) && !preg_match('/x/i', $value) ? $value : '0';
          //well is_numeric() allows hex values (0xFF) but not valid.
          break;
        case 'f':
          $new_args[] = (float)$value; break;
        case 'b': // binary data
          $new_args[] = "'".urlencode($value)."'"; break;
      }
    }
    
    $query = vprintf($original_query, $new_args);
    //mysql_query($query) if you are testing with database
       
      
  }
  else{
      $query = $query; // if no arguments :P :P :P
      echo $query;
      //mysql_query($query) if you are testing with database
  }
}

//As a test
$uid = 5;
$name = "name";
database_query("SELECT FROM node WHERE text='%s' AND uid=%d AND name='%s'", $name, $uid, $name);

well i dropped printf since there was no way it was going to work with it and used vprintf instead :D and i fixed the issue of array_combine overlapping values because keys needs to be unique so i made them so
now it's working correctly..

rolf wroteIt is better, but...
- Keep it simple, I would suggest u use my code.

Well for the time being i am not using the one i created nor yours, i have another one that is working perfectly so until i do extended testing on both, i won't risk to use them. but thanks :)

rolf wrote- I am against type checking for integers and such, it must be done at the javascript level

i think you ought to check types at both levels, html and javascript can be manipulated(doh) so one can send you text in a input that you've expected to be a number therefore an injection is likely.

rolf wrote- There is no default case in your switch, if the % is part of the sql syntax or if the letter after it is unexpected, then the behavior of your function is undefined

yes you are 100 per cent right, i can't use YET % as a part of mysql syntax in the current function, but i am going to do something about it ;)
and imo you shouldn't allow the input of more or less arguments(parameters) than required because you'll have an invalid query or you might mask some nasty bugs this way in other parts of your code that you won't be able to pinpoint fastly...

edit: with break; it works :P... silly

well, i made a quick fix for using % in a query, i had to use preg_replace :| meh

function database_query() {
  $args = func_get_args();
  $q = array_shift($args); // remove first array entry
  if (isset($args[0]) and is_array($args[0])) { 
      // Support for 'All arguments in one array' syntax
    $args = $args[0];
  }
  $keys = explode('%', $q);
  array_shift($keys);
  $i = 0; 
  foreach($keys as &$el){
    $i++; 
    $el = substr((string)$el,0,1)."$i"; // concatenated $i to make unique keys 
  }
    
  if(isset($args) && count($args)!=0){
      $args = array_combine($keys, $args);
      
      foreach($args as $key => &$value){
       $key = substr($key, 0, 1);
      switch ($key) {
        case 'd': 
          $value = $value; break; 
        case 's':
          // WARNING: i am only using urlencode for testing without database interaction
          // for testing with a database, replace urlencode with mysql_real_escape_string for example
          $value = urlencode($value); break;
        case 'n':
          // Numeric values have arbitrary precision, so can't be treated as float.    
          $value = is_numeric($value) && !preg_match('/x/i', $value) ? $value : '0';
          //well is_numeric() allows hex values (0xFF) but not valid.
          break;
        case 'f':
          $value = (float)$value; break;
        case 'b': // binary data
          $value = "'".urlencode($value)."'"; break;
      }
    }
    $q = preg_replace('/\(pc\)/', '%%', $q);
    $q = vsprintf($q, $args);
    
    echo $q;

       
      
  }
  else{
      echo $q;
      //mysql_query($q) if you are testing with database
  }
}

//As a test
$uid = 5;
$name = "name";
$name1 = 'dido<d>';
database_query("SELECT FROM node (pc)WHERE text='%s' AND uid=%d AND name='%s'", $name1, $uid, $name); // (pc) is replaced by %.. dammit i had to use preg_replace after all :/

rolf wroteless code = less bugs

That's not necessarily true...

mysql_real_escape = no injection

okay so you're escaping numbers, hmmm... well you're happy with it :P

also a follow up for a previous question, you asked why i use mysql_real_escape_string instead of htmlentities for escaping html, well when you use htmlentities you do escape html but you won't be able to make other operations on it like strip_tags and you'll have those ugly tags showing in the comments on your website for example, or you'd have to decode, do operations, then encode which is quite unnecessary... i treat html as any normal text when entering to the database, the philosophy that i believe in is to store in as is(if type is correct) and filter out nasty stuff(e.g. xss attacks) on output

well i'll do some extended testing on these two functions, and benchmark them(when i have free time) and the current one i am using... w it's the only objective way to make a proper judgement, well anyway thanks again :) it's cool to have multiple options :D this thread had been/is a great fun(will continue to be :P)... i am going to open another thread for discussing how to prevent xss attacks http://www.lebgeeks.com/forums/viewtopic.php?pid=22744#p22744, great to see others experiences in this subject