PHP SEARCH SQL - Website

Question

I've this coding that works fine on localhost. But in my website it doesnt, and I guess its due do MARIADB version 10.1.24

Here's the code:

$search1 = @$_GET['valuelo'];
$search2 = @$_GET['valuear'];
$search3 = @$_GET['valueco'];
$search4 = @$_GET['valuero'];
$search5 = @$_GET['valuefl'];
$search6 = @$_GET['valuelu'];
$search7 = @$_GET['valuetr'];
$search8 = @$_GET['valuepo'];



   $sql = "SELECT * FROM location, area, condition, rooms, floor, luxury, trendy, pool
     WHERE 'location' LIKE '%$search1%'
     AND 'area' LIKE '%$search2%'
     AND 'condition' LIKE '%$search3%'
     AND 'rooms' LIKE '%$search4%'
     AND 'floor' LIKE '%$search5%'
     AND 'luxury' LIKE '%$search6%'
     AND 'trendy' LIKE '%$search7%'
     AND 'pool' LIKE '%$search8%'";

Here's the error:

ERROR: Could not able to execute

SELECT  *
    FROM  location, area, condition, rooms, floor, luxury, trendy,
        pool
    WHERE  locationbw LIKE '%%'
      AND  area LIKE '%%'
      AND  condition LIKE '%%'
      AND  rooms LIKE '%%'
      AND  floor LIKE '%%'
      AND  luxury LIKE '%%'
      AND  trendy LIKE '%%'
      AND  pool LIKE '%%'

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'condition, rooms, floor, luxury, trendy, pool WHERE locationbw LIKE '%%' ' at line 1

Please help me! I see everything right

First, you have `locationbw` in the error but not in the string. Why is that? Second, you are not concatenating this. Why is that? Your `$sql` value, as shown here, shouldn't work, so I assume this isn't the actual code. It also appears you have an extra ` in there somewhere, based on the error. I would also suggest using pdo so you can used prepared statements for some added security. And, why do you have the @ symbol before your get? You should be checking the existence those variables as well, and sanitizing them accordingly as well — adprocas, Mar 13 '18 at 15:55
Please tell me you haven't got this running on a live web server ... anywhere. — CD001, Mar 13 '18 at 15:55
Your query is a 12 year old script kiddie's wet dream. Please start using [PDO Prepared statements](http://php.net/manual/en/pdo.prepared-statements.php) — IsThisJavascript, Mar 13 '18 at 15:56
Might as go back to PHP 4 and make sure register globals are on as well. — adprocas, Mar 13 '18 at 15:57
Also: [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — CD001, Mar 13 '18 at 15:58
@adpro I changed to locationbw to see the result, but it gave me the same error. It works on Xampp without ' ' on location, area, rooms, floor, luxury etc. I only get this error online. What do I need to change? — BWIS, Mar 13 '18 at 16:00
Are `location, area, condition, rooms, floor, luxury, trendy, pool` tables or columns? It's breaking right there. — aynber, Mar 13 '18 at 16:03
@aynber tables and collumns name. I thought the same, thats why I changed a collumn to locationbw to doesnt match the tablename. But the error started the same way — BWIS, Mar 13 '18 at 16:05
I'd really suggest rethinking your query, so it makes more sense. Right now, it's a mess. — aynber, Mar 13 '18 at 16:07
You still have a bunch of issues. You're using `@$_GET`. That's just suppressing errors for that statement, right? Check to see something exists in that variable rather than just suppressing errors. Also, if you're going to do a straight query like this, you have to sanitize that input. Again, PDO is your friend. It helps you be more secure. https://stackoverflow.com/questions/16195013/pdo-and-mariadb — adprocas, Mar 13 '18 at 16:25
The reason you want to sanitize your input is because someone could easily exploit stuff, throw in come SQL code that does strange things, and compromises all of your data. Popular software has been subject to SQL Injection, including PHPBB years ago. I was able to log into many administrator account by manipulating the username to ignore checking the password. You will certainly make the same mistakes, so using PDO will help you stay more secure. Please consider it, and do not put this SQL in production anywhere. — adprocas, Mar 13 '18 at 16:27
@adpro thank you so much for your advice. I'll look into it! The website is blocked btw... — BWIS, Mar 13 '18 at 16:36

score 1 · Accepted Answer · answered Mar 13 '18 at 16:06

1

Enclose the table-name and column-name into backquote (`) else query will break at the point where you use the MariaDB keyword. In your query "condition" is MariaDB keyword

"SELECT * FROM `location`, `area`, `condition`, `rooms`, `floor`, `luxury`, `trendy`, `pool` WHERE `location` LIKE '%$search1%' AND `area` LIKE '%$search2%' AND `condition` LIKE '%$search3%' AND `rooms` LIKE '%$search4%' AND `floor` LIKE '%$search5%' AND `luxury` LIKE '%$search6%' AND `trendy` LIKE '%$search7%' AND `pool` LIKE '%$search8%'";

answered Mar 13 '18 at 16:06

Jaiprakash Khowal

55
3

So I should change condition to another name right? – BWIS Mar 13 '18 at 16:17
Yes, You can. But always try to keep the table and column name in backquote (`) . – Jaiprakash Khowal Mar 13 '18 at 18:21

Tommaso Belluzzo · Answer 2 · 2018-03-13T16:17:09.187

Your SQL statement is not correct in so many ways that it surprises me it's producing anything good in your development environment.

A good way to proceed in these situations is to concatenate the results of every single table search into a single output, using UNION statements. Provided the tables share a minimum number of identically named and typed fields (signally, Id and Name):

SELECT 'Location' AS Type, Id AS ID, Name AS Ref
FROM location
WHERE location_field LIKE '%$search1%'
  UNION
SELECT 'Area' AS Type, Id AS ID, Name AS Ref
FROM area
WHERE area_field LIKE '%$search2%'
  UNION
SELECT 'Room' AS Type, Id AS ID, Name AS Ref
FROM rooms
WHERE rooms_field LIKE '%$search3%'
  UNION
...

On the top of that, additional checks on the value of the search fields would help building (or skipping) WHERE clauses. Last but not least, use prepared statements instead of building your query strings manually.

Thank you! This is not my area, but Im trying! It looks better! — BWIS, Mar 13 '18 at 16:16

PHP SEARCH SQL - Website

2 Answers2