Sign in to follow this  

[web] Mysql Query

This topic is 2831 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I have a mysql table of Items, in which one column is 'ItemType'. I have links to the left of my page that will allow you to look at certain types of items based on the categoryID. The links are fairly easy, and take the following form: somepage.php?ItemType=1 I can use a $_GET method to filter the item types by putting it into my search query with the call: $Result = mysql_query("Select * from Table where ItemType = $_GET['ItemType']"); This is easy, but I also want users to be able to look at 'all' items. I figured for this, I could use: somepage.php?ItemType=* but this does not appear to work. I have also tried putting the * in single quotes. Any ideas on what I could do here without having to have a second query? The 'best' format would just be (select * from Table), but I hate having a separate query, especially since there are multiple columns I want to sort in this fashion. Thanks.

Share this post


Link to post
Share on other sites
You're not going to get around special code for this scenario. You could however build the query string by code.

/* Using $_GET directly in DB queries/file system access/process creation begs for being hacked. Inform yourself about SQL injection. */
$itemType = properly_quote_foreign_input($_GET['ItemType']);
$sql = "select * from Table";
if ('*' != $itemType) {
$sql .= " where ItemType = " . $itemType;
}
$result = mysql_query($sql);


Generally, you should move up a layer from writing raw SQL and write a DBAL (DataBase Abstraction Layer) to build queries and implement security measures such as quoting.

Share this post


Link to post
Share on other sites
This is very bad. Just implement something like "All" or anything other than direct SQL syntax. What if someone types in something like this:

HaHa'; DROP TABLE someTable;

Sure item 'HaHa' doesn't exist, but the "someTable" has now been deleted!

Share this post


Link to post
Share on other sites
I understand the SQL injection problem. I have coded in php/mysql for many years and have admined several websites. My code uses mysql_escape_strings() and other security tests to prevent such attacks (checking vs. a list of acceptable inputs, etc). I am ok on the security front. So, lets re-focus on what I'm asking.

Konfusius's post solves the 1 filter riddle (which isn't very difficult). But the big problem comes in with multiple filters. For example:

somepage.php?ItemType=1&Size=2&Color=*&Weight=3&Height=7&etc=4

I'm just looking for a nice clean method of creating this query that doesn't involve a lot of manual coding of if statements.

Share this post


Link to post
Share on other sites
If you have the acceptable column names in an array or dictionary, you can build the query string something like this:

$columns = array("ItemType", "Size", "Color", "Weight", "Height", /* ... */);

$query = "Select * from Table";

$count = 0;
foreach($_GET as $variable => $value)
{
if(array_search($columns, $variable)) {
if($count == 0) {
$query .= " where ";
} else {
$query .= " and ";
}
// Using raw $variable is safe as it is the same as one of our array elements
$query .= $variable . " = " . escape_func($value);
$count++;
} else {
// error handling
}
}

// run query




You could add code to detect the wildcard, or you could just omit such columns from the query string.

There might be better functions to use, I'm not very familiar with PHP.

Share this post


Link to post
Share on other sites
Well, if you insist, you can do
somepage.php?ItemType=ItemType

$Result = mysql_query("Select * from Table where ItemType = $_GET['ItemType']");
which will always evaluate to true.

Still, I would never ever do something like that.

Share this post


Link to post
Share on other sites
Quote:
Original post by Cygnus_X
I understand the SQL injection problem. I have coded in php/mysql for many years and have admined several websites. My code uses mysql_escape_strings() and other security tests to prevent such attacks (checking vs. a list of acceptable inputs, etc). I am ok on the security front.

mysql_escape_string has been deprecated since 2002 as it's not safe, so that last assertion seems false. mysql_real_escape_string is what you should use if you absolutely insist on building up SQL from strings. Such a shame that PHP doesn't seem to support parameterized queries, but there you go.

Quote:
I'm just looking for a nice clean method of creating this query that doesn't involve a lot of manual coding of if statements.

For every legitimate key and value specified in the URL, add "key=value" to an array.

At the end, if the array is not empty, add "WHERE " to the query and append the result of implode(" AND ", $array) to generate your WHERE clause.

Share this post


Link to post
Share on other sites
Quote:
Original post by Kylotan
Such a shame that PHP doesn't seem to support parameterized queries, but there you go.


It does, using PDO: PDOStatement, but it's a bit cumbersome.

That's why I always use Zend_Db (which I see as PHP standard library), or other DBAL.

Share this post


Link to post
Share on other sites
ORs have generally have a performance hit as their harder to index.

It might be faster for the DB to do
SELECT *
FROM wibble
WHERE var = $search
UNION
SELECT *
FROM wibble
WHERE $search = ''

I'd love the query optimiser to be clever, but it seems it's not.

Share this post


Link to post
Share on other sites

This topic is 2831 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this