[web] Mysql Query
Author
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.
You're not going to get around special code for this scenario. You could however build the query string by code.
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.
/* 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.
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!
HaHa'; DROP TABLE someTable;
Sure item 'HaHa' doesn't exist, but the "someTable" has now been deleted!
Author
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.
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.
If you have the acceptable column names in an array or dictionary, you can build the query string something like this:
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.
$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 queryYou 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.
Well, if you insist, you can do
Still, I would never ever do something like that.
somepage.php?ItemType=ItemType$Result = mysql_query("Select * from Table where ItemType = $_GET['ItemType']");Still, I would never ever do something like that.
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.
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.
I don't know PHP but what about something like:
Select * from Table where ItemType = $_GET['ItemType'] or $_GET['ItemType'] = '''
Select * from Table where ItemType = $_GET['ItemType'] or $_GET['ItemType'] = '''
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.
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
