query wildcards

[falsepride]

i need to query a bunch of entries from a table from variable columns with a wild card before and after the search term. thats vague i know. so heres my query, but i don't think its a valid query.

mysql_query("SELECT * FROM livestock WHERE" . $_GET['zone'] . "LIKE '%"  . $_GET['name'] . "%'");

[bdl]

Is there an error returned from the query? Take a look at the string MySQL sees:

(as an example, let's say $_GET['zone'] is 'column1' and $_GET['name'] is 'Bob')

SELECT *
FROM livestock
WHEREcolumn1LIKE '%Bob%'

Doesn't look right, does it? You're not formatting the string properly; make sure you leave some space between the WHERE clause and the column name, e.g.

SELECT *
FROM livestock
WHERE column1 LIKE '%Bob%'

Now, that's an absolutely valid query, but is it what you want? Refer to the MySQL manual page for the LIKE operator and basic pattern matching. If you need to control a more specific pattern, look into regular expressions.

In addition, it's important to be aware that using unvalidated, unescaped GET (or POST) data straight in your query is a Very Bad Idea, especially if you're using 'dynamic column names'. I'd suggest storing a list of allowed columns in an array and only allowing those columns to be accessed.

[falsepride]

using $_GET directly in my query won't be a problem in this case. this page is only going to be accessed from a javascript xmlhttprequest. the javascript will limit what column names get put into the queries.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%tri%'' at line 1

is the error i got from the query

[falsepride]

i exceed my limit to edit my last post, so yea bdl you were right i needed to add some space to my query

$result = mysql_query("SELECT * FROM livestock WHERE " . $_GET['zone'] . " LIKE '%" . $_GET['name'] . "%'");

[bdl]

Originally Posted by falsepride

using $_GET directly in my query won't be a problem in this case. this page is only going to be accessed from a javascript xmlhttprequest. the javascript will limit what column names get put into the queries.

I hate to poke holes in your logic, but one of the easiest things a malicious user can do is simply view your page source and understand how the script accepts values, bypass JavaScript altogether and create their own form to send data directly to your PHP script. It's also as easy as a couple of mouse clicks to disable JavaScript in the browser.

You should always double up on filtering data; using JavaScript to do it up front is great, it can save time on multiple page requests kicking back to the user until they give you what you expect. BUT, if the user disables it (either because they look at it from the viewpoint of their own security, or do it specifically to bypass your checks) you have to have a backup in the server side code to perform the same task. At the most, all that happens is the data is checked twice.

[falsepride]

i only meant it won't be a problem for the dynamic column names. i am going to start a new thread actually, talking about security. and buffering variables before they get put into sql queries. speaking of, i think i might as well do that now.