Search query with validation

Question

quicky one, I'm creating search query and I'm stuck with one problem... with this code.

this code is working perfectly but I need to add this validation to it, problem comes when I add this validation userid='$id', the search query is not working with it. Can someone put me on the right way? I'm missing something here. The value of $id is true for sure. This is the whole code part for this search query the result is ok without validation.

else if($_POST['filter1'] == "po_boravistu")
        {
            if($row_12['admin_id'] < 1)
                {
            //FILTER 10 po boravištu
$sqlCommand = "SELECT * FROM albums WHERE userid='$id' AND WHERE bor_mjesto LIKE '%$searchquery%' OR bor_kucni_broj LIKE '%$searchquery%' OR
bor_postanski_broj LIKE '%$searchquery%' OR bor_ulica LIKE '%$searchquery%' OR bor_opcina LIKE '%$searchquery%'";
                }
        }

Whole script is working fine without userid='$id' in it, but when I add that validation script drops dead.

Please post the code for the query with the ID constraint added. Also, have you made sure that $id contains the value you want it to contain? — octern, May 07 '12 at 18:23
@BrianWarshaw I have edit code up with validation, the $id value is ok and result is ok without userid='$id'. So I'm doing something wrong. — Red dwarf, May 07 '12 at 18:40

score 3 · Accepted Answer · answered May 07 '12 at 18:42

Your SQL is invalid because you have 2 WHERE clauses.

Change it to:

$sqlCommand = "SELECT * FROM albums WHERE userid='$id' AND (bor_mjesto LIKE  %$searchquery%' OR bor_kucni_broj LIKE '%$searchquery%' OR bor_postanski_broj LIKE '%$searchquery%' OR bor_ulica LIKE '%$searchquery%' OR bor_opcina LIKE '%$searchquery%')";

(note the use of parentheses to surround the multiple "OR" statements)

score 2 · Answer 2 · answered May 07 '12 at 18:42

2

You need to drop the second WHERE (and add some parentheses), like this:

$sqlCommand = "
SELECT * 
FROM 
albums 
WHERE 
userid='$id' 
AND 
(
bor_mjesto LIKE '%$searchquery%' OR 
bor_kucni_broj LIKE '%$searchquery%' OR
bor_postanski_broj LIKE '%$searchquery%' OR 
bor_ulica LIKE '%$searchquery%' OR 
bor_opcina LIKE '%$searchquery%'
)
";

You only have to use WHERE once per query. Also, you don't need to add the single quotes around $id here, as it's an integer value.

Also, I'm not sure where your variables are coming from, but you'll probably want to escape them using mysql_real_escape_string before putting them into this query. Finally, SELECT * is almost always a mistake: only select the rows you really need. That'll save you a bit of time :)

answered May 07 '12 at 18:42

Daan

3,308
21
19

@Daan What if I'm having many querys in one page? Is there simple way to do the `mysql_real_escape_string` without doing that on every query for every value? Maybe I'm misunderstanding the `mysql_real_escape_string` ? – Red dwarf May 07 '12 at 18:59
If you're using a variable more than once (like `$searchquery` in your example), you just need to escape it once. Put this before your query: `$searchquery = mysql_real_escape_string($searchquery);`. Afterwards, the string `$searchquery` will be safe for use in MySQL queries. This helps prevent problems if you have single quotes in `$searchquery`: without escaping these, the single quotes would be interpreted as ending the string, and thus lead to a MySQL syntax error. Escaping user input properly also protects you against [SQL injection](http://stackoverflow.com/a/601524/1185355). – Daan May 07 '12 at 19:07
@Daan So if I'm not misunderstanding, the mysql_real_escape_string should be used only if the variable is used more then once? Is the mysql_real_escape_string changing somethin else then single quotes? And thanks for clearing my view :) – Red dwarf May 07 '12 at 19:21
1

No, sorry, I should have been clearer. You need to escape every variable that goes into your MySQL queries using `mysql_real_escape_string`. This function escapes everything that could cause the MySQL query interpreter to misinterpret your query. However, these changes will not survive into the database, i.e. the single quotes will not be escaped inside your actual database. If you want to write even better code, you can use the [PDO library](http://www.kitebird.com/articles/php-pdo.html) instead of the standard `mysql_` library; PDO takes care of escaping automatically. – Daan May 07 '12 at 19:30

Search query with validation

2 Answers2