sprintf query construction

[Jonny___Gut]

Hi There, hoping someone can help me out of this hole.

I'm creating a function that will update a number of records from an SQL database, the variables that need to be updated are stored in an array ($array) and I want to use the <a href="http://uk2.php.net/manual/en/function.mysql-real-escape-string.php">sprintf / mysql_real_escape_string</a> construction to avoid SQL injection attacks.

$table = "artists"; $row = "1";

$sql = 'sprintf("';	
$sql .= "UPDATE %s SET ";
for (reset($array); list($key) = each($array);) {
	$sql .= "$key = '%s', ";
	$sprint[$i] = $array[$key]; $i++;   // counter used for later
}

$sql = substr_replace($sql,"",-2);  // trim the last comma and space.

$sql .= " WHERE `pk` = '%s'" . '", '; 
$sql .= "mysql_real_escape_string($table), "; // $table contains the tablename.

$count = (count($sprint));
for ($i=0; $i<$count; $i++) {  // dump the other escape (%s) values.
	$sql .= "mysql_real_escape_string($sprint[$i]), ";
}

$sql .= "mysql_real_escape_string($row));";  // finish it off with the row escape value.


echo "output = $sql";

This will now out put the correct syntax..

$sql wrote:

sprintf("UPDATE %s SET name = '%s', label = '%s' WHERE pk = '%s'", mysql_real_escape_string(artists), mysql_real_escape_string(deftones24), mysql_real_escape_string(universal), mysql_real_escape_string(1));

but when I try to do a query on it...

if (!$result = mysql_query($sql)) {
	die ("SQL FAILED -".mysql_error());
}

I get...

SQL FAILED -You have an error in your SQL syntax near 'sprintf("UPDATE %s SET name = '%s', label = '%s' WHERE pk = '%s'", mysql_real_' at line 1

presumably because it is trying to run the whole string including the sprintf bit, instead of actually parsing it.

Anyone know what I'm getting at here? I'm sure I'm missing something really obvious, but it's been bugging me for a while now! 🙂

Thanks
Jonny.

[thorpe]

That is because sprintf() and mysql_real_escape() are functions. They way you have created it they are just part of your string.

You'll want to create your query string, then run it through these functions.

[Jonny___Gut]

Thanks for the reply, thorpe!

So, I'm presuming...

sprintf($constructedSQL, mysql_real_escape_string($args));

So the first bit is easy - but I have an undetermined number of "mysql_real_escape_string"'s to add to the end of it (eg: one query may have 4 to replace, another may have 10!) - how would I get around that?

Thanks again
jonny.

[thorpe]

Just run the whole query through mysql_real_escape_string(). eg;

sprintf(mysql_real_escape_string($constructedSQL), $arg1,$arg2,$arg3);

[Jonny___Gut]

Okay, I have tried the following:

// form the sql query.
$sql = "UPDATE %s SET ";
for (reset($array); list($key) = each($array);) {
	$sql .= $key." = '";
	$sql .= "%s";
	$sql .= "', ";
	$sprint[$i] = $array[$key];
	$i++;
}
$sql = substr_replace($sql,"",-2);
$sql .= " WHERE `pk` = '%s'";


// form mysql_real_escape_string
$count = (count($sprint));
$escape = "mysql_real_escape_string($table), ";
for ($i=0; $i<$count; $i++) {
	$escape .= "mysql_real_escape_string("$sprint[$i]"), ";
}
$escape .= "mysql_real_escape_string($row)";


// form the sprintf function.
$query = sprintf($sql, $escape);
echo $sql . ' - ' .$escape . '<br><br>';  // echo the output.

resulting in...

Warning: sprintf() [function.sprintf]: Too few arguments in /disk1/www/www.gutrecords.com/www.intermediaregional.com/htdocs/new/admin/functions/make_admin_changes.php on line 69

UPDATE %s SET name = '%s', label = '%s' WHERE pk = '%s' - mysql_real_escape_string(artists), mysql_real_escape_string(deftones24), mysql_real_escape_string(universal), mysql_real_escape_string(1)

SQL FAILED -Query was empty

help?! 😐

[Jonny___Gut]

thorpe wrote:

Just run the whole query through mysql_real_escape_string(). eg;

sprintf(mysql_real_escape_string($constructedSQL), $arg1,$arg2,$arg3);

Thanks again for the reply!

I know I'm being dense here, something's just not clicking... I don't know how many $arg's there are going to be - don't I need to do some kind of array count and then output the correct number of args?