execute function in string var?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

execute function in string var?

Post by papa »

Hi,

I'm trying to do a dynamic update function but got stuck.

Code: Select all

        //create query
        foreach($values as $value) {
            $mysql_escape .= mysql_real_escape_string($value, $link).", ";
            $vals .= "%s, ";
        }
        $mysql_escape = substr($mysql_escape, 0, -2);
        $vals = substr($vals, 0, -2); 
        
         //MySQL query
        $query = sprintf("INSERT INTO ".$mySQLtable." () VALUES (".$vals."), ", $mysql_escape);
I'm passing an array to my function with the data I want to put into my table. As we don't want an SQL injection I have to add the $mysql_escape string which I want to be executed in the $query.

However I don't know how to store it properly so i get an error:
Warning: sprintf() [function.sprintf]: Too few arguments in C:\wamp\www\test\test.php on line 68
I know it's a simple fix but don't know how to do it.

Thanks
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Re: execute function in string var?

Post by jmut »

Well you have couple of placeholders %s and just one string $mysql_escape hences number of %s != num args to substitute them.
I'd suggest little rewriting your function.
As it is..you require really all fields passed while this is not necessarily true... use the INSERT INTO bla (col1,col2) values (val1,valu2) syntax.
Then input data will probably be column->value pair assoc array.
array_keys,array_values,implode functions might be of interest. Don't do this weird substr. Once you mysql_real_escape you can use implode to built the final string.
Don't forget the quotes. Give it a try and let us see how it worked.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: execute function in string var?

Post by VladSun »

jmut, you are fast :)

@papa
As jmut suggested, array_walk() with mysql_real_escape_string().
As jmut also suggested, you can use keys of the array as column names and values of the array as values for these columns.
I think call_user_func_array() may be in use (by calling sprintf), or implode().
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: execute function in string var?

Post by papa »

Thank you very much guys. I got some new ideas now, I'll get back when I've tried some more!
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: execute function in string var?

Post by papa »

Code: Select all

 
function mySQLupdate(...) {
...
 
    //create query
        $cols = array_keys($values);
        $vals = array_values($values);
        foreach($vals as $val) {
            $mysql_escape[] = call_user_func_array("mysql_real_escape_string", array($val, $link));
            is_int($val) ? $placeholder[] = "'%d'" : $placeholder[] = "'%s'";
        }
        print_r($mysql_escape);
        //MySQL query
        $query = sprintf("INSERT INTO ".$mySQLtable." (".implode(",", $cols).") VALUES (".implode(", ", $placeholder).")", implode(", ", $mysql_escape));
Got it to work with hard coded VALUES and the mysql_escape in place. I then added my $placeholder variable and the problem now is that it adds slashes to my values and stripslashes doesn't work.

$placeholder outputs: Array ( [0] => \'My topic\' [1] => \'My article text\' [2] => \'image_url\' [3] => NULL )


My data:

Code: Select all

if(mySQLupdate(array("name"=>"'My topic'", "password"=>"'My article text'", "email"=>"'image_url'", "reg_date"=>"NULL"), "player")) echo "Success!";
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: execute function in string var?

Post by papa »

I know what the error is but have no idea how to solve it at this point.

Code: Select all

Warning: sprintf() [function.sprintf]: Too few arguments in C:\wamp\www\test\test.php on line 71
Invalid query: Query was empty
If I hard code the $placeholder values ('%s', etc) I still get the same error, so seems like I don't have implemented the mysql_real_escape properly even though I don't get an error message...
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Re: execute function in string var?

Post by jmut »

You did it properly with escaping but wrong with calling sprintf. You generate single variable there while you need to have N number of arguments passed to sprintf where N equals number of %s you have.
See http://bg2.php.net/sprintf man. You need to supply sprintf("%s %s",$var1,$var2) and you do sprintf("%s %s",$var1) regardless of fact that var1...contains values(in a single string) for all placeholders.
So as VladSun suggests you should use call_user_func_array to call sprintf with random number of args.
User avatar
papa
Forum Regular
Posts: 958
Joined: Wed Aug 27, 2008 3:36 am
Location: Sweden/Sthlm

Re: execute function in string var?

Post by papa »

Got it finally!

Code: Select all

        $cols = array_keys($values);
        $vals = array_values($values);
        
        //Create MySQL query string
        foreach($vals as $val) {
            $query[] = call_user_func_array("mysql_real_escape_string", array($val, $link));
            switch($val) {
                case "NULL":
                $placeholder[] = "NULL";
                break;
                case is_int($val):
                $placeholder[] = "'%d'";
                break;
                default:
                $placeholder[] = "'%s'";
            }
        }
        $query = array_merge(array("INSERT INTO ".$mySQLtable." (".implode(",", $cols).") VALUES (".implode(", ", $placeholder).")"), $query);
        $query = call_user_func_array("sprintf", $query);
        $result = mysql_query($query) or die('Invalid query: ' . mysql_error());
        return 1;
        mysql_free_result($result);
Not the prettiest code though, ideas?

Thank you very much for the good pointers!!!
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Re: execute function in string var?

Post by jmut »

1. No need to make call_user_func_array on mysql_real_escape_string
2. It's good to escape columns with ` ` (backticks)
3. Not sure this NULL there is really what required. cause it should be without quotes and all to be treated as NULL and not string 'NULL'
4. I would rewrite this line to improve readability

Code: Select all

        
$sql_cols = implode(",", $cols);
$sql_values = implode(", ", $placeholder);
$query_call = array();
$query_call[] = "INSERT INTO {$mySQLtable} ({$sql_cols}) VALUES ({$sql_values})";
$query_call[] = $query;
$query = call_user_func_array("sprintf", $query_call);
 
5. This die() there I guess is just for the purpose of example. Usually insert function would return the PK generated from the new record or exception or false or whatever.
6. Well stuff can always be improved. I suggest you don't try to make it perfect...there is no such thing. As soon as it is clear and readable and does what you want todo just use it. If you find lots of scenarious where this is not ok then should reconsider.
Post Reply