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.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts

    Sorting with Prepared Statement?

    My website allows people to send PM's - just like on Coding Forums - and now I would like to add the ability to SORT PMs BY FIELD.

    For security reasons, I have always used PHP Prepared Statements.

    Here is a sample Prepared Statement used to populate the User's Inbox...

    PHP Code:
        $q1 'SELECT pm.id, r.read_on, r.flag, m.username AS username_from, pm.subject, pm.sent_on
                FROM blah...
                WHERE r.member_id_to = ?
                ORDER BY pm.sent_on DESC'
    ;

        
    $stmt1 mysqli_prepare($dbc$q1);

        
    mysqli_stmt_bind_param($stmt1'i'$memberID);

        
    mysqli_stmt_execute($stmt1);

        
    mysqli_stmt_store_result($stmt1); 

    What is the best way to make it so I can add sorting on the columns??


    Should I...

    1.) Have a Hard-Coded Query for for each scenario, and then use a Case Statement to branch to the appropriate query??

    2.) Can I add a '?' (i.e. Bound Variable) in the "ORDER BY" portion of the query??

    3.) Some other approach??


    Similar to how Coding Forums does things, I would like it so that the Column Headings in the User's Inbox are hyperlinks, and if you click on one (e.g. "From"), it will be a URL with the "Sort Column" and "Sort Order" in the Query String, and then my PHP can grab that and adjust the query above accordingly.

    Hope that makes sense?

    Sincerely,


    Debbie

  • #2
    Regular Coder
    Join Date
    Sep 2002
    Posts
    462
    Thanks
    0
    Thanked 20 Times in 20 Posts
    I would use
    2.) add a '?' (i.e. Bound Variable) in the "ORDER BY" portion of the query??

    But use a number instead of a name:
    Date = 1
    Title = 2
    This way you can capture the request and modify as needed.
    checking for an number value should keep down injections then you can name the orderby column whatever you need.
    At least that's the way we will be handling ours when I get that far.hehehe
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    For projects using MediaTypes (MIMETypes) visit E-BAM.net -(updated weekly)

    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.

  • #3
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by c1lonewolf View Post
    I would use
    2.) add a '?' (i.e. Bound Variable) in the "ORDER BY" portion of the query??

    But use a number instead of a name:
    Date = 1
    Title = 2
    This way you can capture the request and modify as needed.
    checking for an number value should keep down injections then you can name the orderby column whatever you need.
    At least that's the way we will be handling ours when I get that far.hehehe
    Can you help me out with the syntax?

    I'm just used to using a ? after an equal sign, like...
    Code:
    WHERE username = ?
    Sincerely,


    Debbie

  • #4
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,439
    Thanks
    62
    Thanked 537 Times in 524 Posts
    Quote Originally Posted by doubledee View Post
    2.) Can I add a '?' (i.e. Bound Variable) in the "ORDER BY" portion of the query??
    This can't be done (afaik anyway). You can only bind parameters in the values and where sections of a query. You cannot specify a column. When I was trying it the other day i was also unable to bind parameters for sorting.

    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #5
    Regular Coder
    Join Date
    Sep 2002
    Posts
    462
    Thanks
    0
    Thanked 20 Times in 20 Posts
    @tangoforce: Are you talking about:
    WHERE r.member_id_to = ?
    I was just building the query string.
    NO Limits!! DHCreationStation.com
    ------------------------------------------------------------
    For projects using MediaTypes (MIMETypes) visit E-BAM.net -(updated weekly)

    Broken items wanted for tinkerin'! PostItNow@BrokenEquipment.com
    Global Complaint Dept.

  • #6
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by tangoforce View Post
    This can't be done (afaik anyway). You can only bind parameters in the values and where sections of a query. You cannot specify a column. When I was trying it the other day i was also unable to bind parameters for sorting.
    What you posted is what I would have said also.

    (That is why I'm asking for help.)


    However, since this is such a *common* problem...

    And since using Prepared Statements is not only common, but - IMHO - is the only way to write queries in PHP...


    There must be some way to accomplish what I want AND not jeopardize the *security* of my Prepared Statement...


    Any other ideas??

    Sincerely,


    Debbie

  • #7
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by c1lonewolf View Post
    @tangoforce: Are you talking about:

    I was just building the query string.
    I think you missed the *key* point in my OP...

    That being, How can you work in a variable associated with the ORDER BY part of the query when you are using Prepared Statements??


    As Tango pointed out, this may not be possible...


    One of the reasons why Prepared Statements are superior to any other approach, it that they minimize the risk that a hacker can insert a bogus column or value into your query which could lead to "SQL Injection"...

    But that added "security feature" is also making it harder for me to figure out how to make the "ORDER BY" portion of my Prepared Statement *dynamic*...

    See??

    Sincerely,


    Debbie

  • #8
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,439
    Thanks
    62
    Thanked 537 Times in 524 Posts
    Quote Originally Posted by c1lonewolf View Post
    @tangoforce: Are you talking about:

    I was just building the query string.
    No thats the 'where' part. You CAN use a binded parameter there, it's there ORDER BY part that won't accept them.

    Quote Originally Posted by doubledee View Post
    However, since this is such a *common* problem...

    And since using Prepared Statements is not only common, but - IMHO - is the only way to write queries in PHP...


    There must be some way to accomplish what I want AND not jeopardize the *security* of my Prepared Statement...
    What I ended up doing was to just use $variables. Run them through mysqli_real_escape_string() first if they're from the user or if they're hard coded / set by another part of the script thats hard coded it won't really matter a lot.

    Screens grab of my workaround:







    Risks? - I don't see any as it's only the order by and limit clauses at this end of the query so I wouldn't think there could be any harm done there.

    Quote Originally Posted by doubledee View Post
    But that added "security feature" is also making it harder for me to figure out how to make the "ORDER BY" portion of my Prepared Statement *dynamic*...
    Don't lol. I just spent all week working on code on my localhost using binded params only to find that the server doesn't have mysqlnd meaning that I can't get any resultsets out of it. The consequence here is that I'll have to use mysqli_query instead Who at php thought it would be a good idea to remove one of the most well known parts of a query known as the resultset and put it into another driver that isn't installed on every system?
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #9
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Tango,

    Hard to follow your response...

    In the end, what I saw you doing is this...
    Code:
    SELECT blah
    FROM blah
    WHERE field1 = ?
    ORDER BY $sortField $sortOrder

    I think this is still susceptible to a SQL Injection attack like this...
    Code:
    $sortField = "id'; DROP TABLE members; --"
    
    $sortOrder = '';
    Sincerely,


    Debbie

  • #10
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,131
    Thanks
    2
    Thanked 328 Times in 320 Posts
    Using any sort of escape string function won't prevent sql injection in keywords/columns being put into the query statement. Those values aren't strings (enclosed by single-quotes in the query) so the sql injection that doesn't make use of any of the special characters that would be escaped won't be stopped.

    For keywords/columns that come from user input, you must validate that the value is EXACTLY one of the expected values you want to allow before putting it into the query statement.
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #11
    Senior Coder
    Join Date
    Feb 2011
    Location
    Your Monitor
    Posts
    4,439
    Thanks
    62
    Thanked 537 Times in 524 Posts
    Quote Originally Posted by doubledee View Post
    Tango,

    Hard to follow your response...
    Thats cos I was showing a basic example of how I take the user input, check it, and then assign the $column in a switch instead of letting the user supply it directly.

    Quote Originally Posted by doubledee View Post
    In the end, what I saw you doing is this...
    Code:
    SELECT blah
    FROM blah
    WHERE field1 = ?
    ORDER BY $sortField $sortOrder

    I think this is still susceptible to a SQL Injection attack like this...
    Code:
    $sortField = "id'; DROP TABLE members; --"
    
    $sortOrder = '';
    Mysql doesn't take multiple queryes seperated by ; so that extra drop isn't going to happen.

    If you look back at my screen grabs, you'll see that I set tje column variable inside a switch which is hard coded. Therefore no extra injection will make it into my sql because although it uses variables, they've been hardcoded in my switch and not by user input.

    There may be better ways, CFM is the mysqli guru here - I've only recently started using it myself but thats how I've dealt with the issue you're having.
    See my new CodingForums Blog: http://www.codingforums.com/blogs/tangoforce/

    Many useful explanations and tips including: Cannot modify headers - already sent, The IE if (isset($_POST['submit'])) bug explained, unexpected T_CONSTANT_ENCAPSED_STRING, debugging tips and much more!

  • #12
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by CFMaBiSmAd View Post
    Using any sort of escape string function won't prevent sql injection in keywords/columns being put into the query statement. Those values aren't strings (enclosed by single-quotes in the query) so the sql injection that doesn't make use of any of the special characters that would be escaped won't be stopped.

    For keywords/columns that come from user input, you must validate that the value is EXACTLY one of the expected values you want to allow before putting it into the query statement.
    Why the double post?


    Sorry, but I'm not following your response and how it relates to my OP...


    Some comments...

    1.) I exclusively use Prepared Statements because I *thought* that they basically eliminate the risk of SQL Injection attacks.

    Is that not true???


    2.) I would like to provide the ability for a logged in Member to SORT Private Messages by either "From", "Subject" or "Date" in their Inbox.

    I am looking for a *secure* way to do that, and my hope was to not have to get rid of my presumably secure Prepared Statements that I already use.

    So how can I "have my cake and eat it too"???

    Sincerely,


    Debbie

  • #13
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by doubledee View Post
    1.) I exclusively use Prepared Statements because I *thought* that they basically eliminate the risk of SQL Injection attacks.
    Yes.
    2.) I would like to provide the ability for a logged in Member to SORT Private Messages by either "From", "Subject" or "Date" in their Inbox.
    You cannot bind a structural element with a prepared statement. You must provide a variable which has to be escaped with standard mysqli_real_escape_string functionality. Prepared statements only work with binding data, not structure.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #14
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,127
    Thanks
    27
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Fou-Lu View Post
    Yes.

    You cannot bind a structural element with a prepared statement. You must provide a variable which has to be escaped with standard mysqli_real_escape_string functionality. Prepared statements only work with binding data, not structure.
    So you are saying I can't do what I want?


    What about what Tango suggested...
    Code:
    $sortField = some sanitized user input;
    
    $sortOrder = some sanitized user input;
    
    $q1 = "SELECT blah
    FROM blah
    WHERE field1 = ?
    ORDER BY $sortField $sortOrder";
    
    $stmt1 = mysqli_prepare($dbc, $q1);
    
    mysqli_stmt_bind_param($stmt1, 'i', $memberID);
    
    mysqli_stmt_execute($stmt1);
    Sincerely,


    Debbie

  • #15
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    That would be fine. The data has been replaced prior to the preparation.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

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