0

I am making a checkout/cashier software as hobby project, at the moment I am trying to come up with some way to search items in the database.

I got a database from my part-time job which contains about 50,000 items of computer related parts and I want to be able to search through them using keywords or EAN code.

If I enter this it shows the item If I enter this it shows the item

If I enter this it doesn't show the item enter image description here

Am I doing something wrong in my MySQL Query? This is how I come up with these results:

SELECT * 
FROM items 
WHERE EAN LIKE '%" . addslashes($_GET['sTerm']) . "%' 
OR itemName LIKE '%" . addslashes($_GET['sTerm']) . "%' 
OR factoryId LIKE '%" . addslashes($_GET['sTerm']) . "%' 
OR itemId LIKE '%" . addslashes($_GET['sTerm']) . "%';

Where addslashes($_GET['sTerm']) stands for the search bar contents so in this case AAA or from the second image AAA Varta

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Menno van Leeuwen
  • 437
  • 5
  • 25
  • 1
    Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Jun 28 '17 at 12:05
  • @RiggsFolly thanks for pointing this out. I have already had a look at that although even if I do implement a fix at this point for that it wouldn't fix my issue of poor search results. – Menno van Leeuwen Jun 28 '17 at 12:09
  • It might, as you wont have to use addslashes() and potentially alter your search terms in the process – RiggsFolly Jun 28 '17 at 12:10
  • Note: you are missing a closing `"` at the end of this line `OR itemId LIKE '%" . addslashes($_GET['sTerm']) . "%';` – RiggsFolly Jun 28 '17 at 12:11
  • 1
    Look into elasticsearch – Jan-Willem de Boer Jun 28 '17 at 12:12
  • Check that the white space between the words is identical in the table as in the query. Use `HEX(...)` to help verify. – Rick James Jul 04 '17 at 17:40
  • Consider `FULLTEXT` indexing -- a lot faster, but is limited to "words". – Rick James Jul 04 '17 at 17:41

0 Answers0