sprintf help needed

I'm using sprintf() to formulate my sql statements, to prevent sql injections.

The problem I'm having is that some of my variables I use to construct my queries sometimes have data and sometimes not. They are filters and if there are no filters in effect, they are empty. But in the query single quotes display and it stops the query from working:


$php_SQL = sprintf("SELECT * FROM Employees
WHERE '%s' '%s' '%s'
AND company_index = '%d' ORDER BY last_name",
mysql_real_escape_string($name_filter),
mysql_real_escape_string($cell_phone_filter),
mysql_real_escape_string($dept_filter),
mysql_real_escape_string($_SESSION["php_g_company_index"]));


$name_filter may be empty but the '' shows up in the query and stops the query from working. I take out the '' and it seems to work ok that way but I'm not sure that is correct MySQL query syntax to do that?


$php_SQL = sprintf("SELECT * FROM Employees
WHERE %s %s %s
AND company_index = %d ORDER BY last_name",
mysql_real_escape_string($name_filter),
mysql_real_escape_string($cell_phone_filter),
mysql_real_escape_string($dept_filter),
mysql_real_escape_string($_SESSION["php_g_company_index"]));


Variables $cell_phone_filter and $dept_filter may or may not contain values.

Thanks for any help.

The WHERE syntax appears to be way off. What are some values that $name_filer, $cell_phone_filter, and $dept_filter could hold?

If the variables hold things like
x='y'
and so on then that is fine since it will build it up nicely but you cannot use mysql_real_escape_string on the data since it will escape the single quotes

On a side note, you may wish to use my mressf (http://www.jaygilford.com/php/sprintf-and-mysql_real_escape_string-all-in-one-function/) function in future for other queries similar to this, which alleviates the need for escaping each input of the query string's data passed by sprintf. Your query would look like

$php_SQL = mressf("SELECT * FROM Employees
WHERE '%s' '%s' '%s'
AND company_index = '%d' ORDER BY last_name",
$name_filter,
$cell_phone_filter,
$dept_filter,
$_SESSION["php_g_company_index"]);

but would yield the same output as your first example

$dept_filter would hold a string something like thisg:

dept_no = '21'

What I'm trying to do is prevent sql injections and I read a tutorial that recommended using the sprintf() function (in the manner I'm using) as a way to prevent sql injections.

Would mressf() also prevent sql injections?

You want to escape the value itself. Not the entire field ='xxx', just the xxx. Otherwise you end up escaping the single-quotes and get field=\'xxx\'

mressf works exactly the same as you've put your code only it doesn't require you to add mysql_real_escape_string to each value, it does it automatically, however you will face the same problems with it if you escape the whole string instead of the value only, as kbluhm has pointed out

I'm trying your mressf() but I am still getting slashes in front of my single quoates when I use my name_filter var.


$name_filter = " AND last_name LIKE '$php_filter_employee_name%' ";



$php_SQL = mressf("SELECT * FROM Employees WHERE
%s %s %s AND company_index = '%d'
ORDER BY last_name",
$name_filter,
$cell_phone_filter,
$dept_filter,
$_SESSION["php_g_company_index"]);


Here is the query output:


SELECT * FROM Employees WHERE last_name LIKE \'i%\' AND company_index = '38' ORDER BY last_name


cell_phone and dept filters are empty.

You don't seem to understand... you must escape only the value.

$something = 'code that shouldn\'t be injected';

// correct
$sql = "SELECT * FROM `whatever` WHERE `something` = '" . mysql_real_escape_string( $something ) . "' LIMIT 1";
// output: SELECT * FROM `whatever` WHERE `something` = 'code that shouldn\'t be injected' LIMIT 1

// incorrect... what you're trying to do (basically)
$sql = "SELECT * FROM `whatever` WHERE " . mysql_real_escape_string( "`something` = '" . $something . "'" ) . " LIMIT 1";
// output: SELECT * FROM `whatever` WHERE `something` = \'code that shouldn\'t be injected\' LIMIT 1


See the difference?