Sign in to follow this  
Cygnus_X

[web] Mysql Query

Recommended Posts

Cygnus_X    359
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
demonkoryu    980
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
UltimaX    468
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
Cygnus_X    359
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
rip-off    10976
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
Wan    1366
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
Kylotan    9875
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
demonkoryu    980
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
ROBERTREAD1    100
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

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