Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jun 2007
    Location
    Los Angeles
    Posts
    545
    Thanks
    81
    Thanked 5 Times in 5 Posts

    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 Code:
    $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 Code:
    $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.
    RalphF
    Business Text Messaging Services
    https://www.MobileTextingService.com

  • #2
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    The WHERE syntax appears to be way off. What are some values that $name_filer, $cell_phone_filter, and $dept_filter could hold?

  • #3
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    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 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
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • #4
    Regular Coder
    Join Date
    Jun 2007
    Location
    Los Angeles
    Posts
    545
    Thanks
    81
    Thanked 5 Times in 5 Posts
    $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?
    RalphF
    Business Text Messaging Services
    https://www.MobileTextingService.com

  • #5
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    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\'

  • #6
    Regular Coder
    Join Date
    Dec 2009
    Location
    UK
    Posts
    495
    Thanks
    0
    Thanked 58 Times in 58 Posts
    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
    My site: JayGilford.com
    Resources:
    PHP Pagination Class | Getting all page links | Handling PHP Errors properly
    If you like a users help, show your appreciation with the rep and thanks buttons :)

  • Users who have thanked JAY6390 for this post:

    rfresh (02-11-2010)

  • #7
    Regular Coder
    Join Date
    Jun 2007
    Location
    Los Angeles
    Posts
    545
    Thanks
    81
    Thanked 5 Times in 5 Posts
    I'm trying your mressf() but I am still getting slashes in front of my single quoates when I use my name_filter var.

    PHP Code:
    $name_filter " AND last_name LIKE '$php_filter_employee_name%' "
    PHP Code:
    $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:

    PHP Code:
    SELECT FROM Employees WHERE last_name LIKE 'i%\' AND company_index = '38 ORDER BY last_name 
    cell_phone and dept filters are empty.
    RalphF
    Business Text Messaging Services
    https://www.MobileTextingService.com

  • #8
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    You don't seem to understand... you must escape only the value.
    PHP Code:
    $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?
    Last edited by kbluhm; 02-11-2010 at 07:34 AM.

  • Users who have thanked kbluhm for this post:

    rfresh (02-11-2010)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •