Page 1 of 1

SELECT statement with a wildcard

Posted: Wed Jul 28, 2004 11:11 am
by evilmonkey
Hello. I'm trying to make a select statement based on user input. My dillema is this: there may or may not be user input for a particular variable. The user has the option to search (for example) by category, by username, by both, or by no parameters (lists all results). The way I figured I'd do it is this:

Code: Select all

<?php
if (isset($_GET['category'])) {
$category = $_GET['category']; }
else {
$category = //wildcard here, because i want results from all categories
}
if (isset($_GET['username'])) {
$username = $_GET['username']; }
else {
$username = //same dillema
}
//finally, the query
$query = "SELECT * FROM `table` WHERE `category` = $category AND `username`= $username AND /*more variables */";
?>
So if $_GET['category'] or $_GET['username'] are unset, I want MySQL to pull all the results. I tried both the * and the %, but to no avail. What is the wildcard symbol?

Thanks!

Posted: Wed Jul 28, 2004 11:16 am
by feyd
" LIKE '%' " instead of " = '' "

Posted: Wed Jul 28, 2004 11:18 am
by evilmonkey
Hmm, and if there's a value? with LIKE 'value' work? What is LIKE anyway?

Posted: Wed Jul 28, 2004 11:20 am
by hedge
I'd build the query dynamically by adding where clauses as necessary. something like this:

Code: Select all

q = 'select * from table where 1=1'
if (?) q .= ' and col1=' . $val
.....
just be careful with your quotes... this is a place where sql injection attacks could occurr.

Posted: Wed Jul 28, 2004 11:24 am
by evilmonkey
SQL injection won't be a problem because the user isn't entering any data. It's all from drop down boxes...Hmm...now that I think about it, I'm using $_GET[]...maybe I should use $_POST[] instead?

EDIT: I just realized, that way will result in vary messy code for me...I'm validating user input instead, so something like if ($_GET['gender'] == "F" || $_GET['gender'] =="M") instead of just if (isset($_GET['gender'])). (just as an example)

Posted: Wed Jul 28, 2004 11:27 am
by hedge
Well you still need to watch for SQL injection... you can't assume that your script will necessarilly be called by the html you build.

It's trivial to grab the html and modify it to post to your script with values of my choosing. This can be done with a GET or a POST, POST really isn't any more secure than GET.

Posted: Wed Jul 28, 2004 11:31 am
by evilmonkey
Hmm, I see your point...look above for my edit, do you thik that's sufficient security?