If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Enjoy an ad free experience by logging in. Not a member yet? Register.

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.

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

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

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.

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...

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*...

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

Originally Posted by doubledee

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.

Originally Posted by doubledee

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?

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.

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.

Originally Posted by doubledee

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.

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.

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 ;)

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.