0

I need a sql query to first check for a match WHERE column is eqaul to X1 and if no match here, search every row.

This is my current SQL statement:

SELECT name, image_url FROM `".$config_databaseTablePrefix."products` WHERE name LIKE '$queryString%' LIMIT $numberOfProducts

But I need it to check for a match WHERE e.g columnName = XX, and if no match is found look in all other rows.

How can I achieve this?

femtoRgon
  • 32,893
  • 7
  • 60
  • 87
Troels Johannesen
  • 725
  • 2
  • 7
  • 30
  • 1
    Are you using both mysql and sql-server? They are not the same thing and the syntax varies quite a bit. – Sean Lange Nov 04 '15 at 16:19
  • There's a syntax error in the query, and you're vulnerable to injection. Kill this code before it hatches. Syntax: `FROM \`".$config_databaseTablePrefix."products\`` expands to `FROM \`db.products\`` whereas it should be `FROM \`db\`.\`products\`` or just `FROM db.products` – Elias Van Ootegem Nov 04 '15 at 16:20
  • So you want to find for a specific product or all products? This doesn't make a lot of sense. And you need to deal with your sql injection issue as suggested already. – Sean Lange Nov 04 '15 at 16:21
  • 1
    @EliasVanOotegem he could just be using prefixed tables, so it would be `myprefix_products`, which is just fine. Though I agree that PDO prepare or mysqli_real_escape_string would be preferable. – moorscode Nov 04 '15 at 16:24
  • 1
    @moorscode it is the LIKE expression that is of grave concern from an injection vulnerability. – Sean Lange Nov 04 '15 at 16:25
  • @moorscode: `mysqli` supports prepared statements all the same, no need to revert to `real_escape_string` if you don't have to. – Elias Van Ootegem Nov 04 '15 at 16:26
  • How can I prevent injection @SeanLange ? If I can't use LIKE - what can I then use to match a string with a value in DB ? – Troels Johannesen Nov 04 '15 at 16:35
  • I use: $queryString = $db->real_escape_string($_POST['queryString']); isen't that enough ? – Troels Johannesen Nov 04 '15 at 16:37
  • See this thread http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1. – chris85 Nov 04 '15 at 16:38
  • @TroelsJohannesen: It isn't it doesn't (and shouldn't) escape wildcards (`_` and `%` for example, and you have to be sure that you're only escaping the characters once, or you could end up with weird bugs being reported and messy looking data – Elias Van Ootegem Nov 04 '15 at 16:44

2 Answers2

0

You can use like:

SELECT name, image_url FROM `".$config_databaseTablePrefix."products` WHERE (name LIKE '$queryString%') OR (some other rules....) LIMIT $numberOfProducts;
fico7489
  • 7,931
  • 7
  • 55
  • 89
  • But don't ignore the comments about sql injection! – Mikel Bitson Nov 04 '15 at 16:24
  • 2
    1) You still have the syntax error I mentioned in my first comment and 2) OP is asking what those _"some other rules"_ are and 3) [bobby-tables](http://bobby-tables.com) will crush you if you use this query – Elias Van Ootegem Nov 04 '15 at 16:25
  • why? maybe app doesn't have to be secure because it is used internaly. – fico7489 Nov 04 '15 at 16:26
  • 1
    @fico7489: Never trust the network. Even if it's only used internally: people will find out about input like `%'; --` having some special powers, and they will end up using it – Elias Van Ootegem Nov 04 '15 at 16:27
  • 3
    @fico7489 there is never an excuse to not parameterize queries. Just because it is used internally doesn't mean anything. Also, maybe it is internal today but next week it is exposed to the world. It is so simple to parameterize queries there is no excuse not to do it. – Sean Lange Nov 04 '15 at 16:27
  • 3
    @fico7489: Even if the client isn't trying to exploit the injection vulnerability, an accidental single quote in the input is enough to mess things up. Better to be safe than sorry – Elias Van Ootegem Nov 04 '15 at 16:30
0

As mentioned in comments try to fix sql injection in first place then try something like this

IF (SELECT 1 = 1 FROM table
WHERE name LIKE 'queryString%'  ) THEN
BEGIN
SELECT name, image_url 
FROM table
WHERE name LIKE '$queryString%'     
LIMIT $numberOfProducts;
END;
ELSE
BEGIN
SELECT name, image_url 
FROM table
LIMIT $numberOfProducts
END;
END IF;

Referred from this answer

Community
  • 1
  • 1
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172