Page 1 of 1
execute function in string var?
Posted: Thu Oct 02, 2008 9:08 am
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
Re: execute function in string var?
Posted: Thu Oct 02, 2008 9:43 am
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.
Re: execute function in string var?
Posted: Thu Oct 02, 2008 9:51 am
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().
Re: execute function in string var?
Posted: Thu Oct 02, 2008 10:12 am
by papa
Thank you very much guys. I got some new ideas now, I'll get back when I've tried some more!
Re: execute function in string var?
Posted: Thu Oct 02, 2008 12:14 pm
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!";
Re: execute function in string var?
Posted: Fri Oct 03, 2008 2:01 am
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...
Re: execute function in string var?
Posted: Fri Oct 03, 2008 4:21 am
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.
Re: execute function in string var?
Posted: Fri Oct 03, 2008 7:21 am
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!!!
Re: execute function in string var?
Posted: Fri Oct 03, 2008 2:18 pm
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.