Search query, capital letters

Question

Can someone help me with this. The form needs to make no difference between capital letters and normal letters.

$raw_results = mysql_query("SELECT * FROM product
            WHERE (`naam` LIKE '%".$query."%') OR (`titel` LIKE '%".$query."%') OR (`druk` LIKE '%".$query."%')") or die(mysql_error());

The charset is latin1_bin

Extreme SQL Injection Alert – allen213 Sep 19 '13 at 10:50 — allen213, Sep 19 '13 at 10:50
"the form" and you show us a query? – Loko Sep 19 '13 at 11:37 — Loko, Sep 19 '13 at 11:37

JTC · Accepted Answer · 2013-09-19T11:58:47.417

1

You could use UPPER in mysql.

$raw_results = mysql_query("SELECT * FROM product
            WHERE (UPPER(naam) LIKE UPPER('%".$query."%')) OR (UPPER(titel) LIKE UPPER('%".$query."%')) OR (UPPER(druk) LIKE UPPER('%".$query."%'))") or die(mysql_error());

edited Sep 19 '13 at 11:58

answered Sep 19 '13 at 10:42

JTC

3,344
3
28
46

Invalid use `LIKE '%".UPPER($query)."%'` or your fixed one `LIKE '%UPPER(".$query.")%'`. Function should be outside of the string `LIKE UPPER('%".$query."%')`. – Glavić Sep 19 '13 at 10:44
Thanks and sorry about that. – JTC Sep 19 '13 at 10:48
Can to answer this question.. https://stackoverflow.com/q/56066980/11065582 – May 10 '19 at 03:19

score 1 · Answer 2 · answered Sep 19 '13 at 10:45

That depends of collation that you're using for your field. With default latin1_general_ci collation (or similar _ci-ended) comparison and string functions will be insensitive to case.

So, you have two options. First - use strtolower() in your PHP application or LOWER() in MySQL itself - to convert your search strings to lower case (i.e. make them independent of their original content case). Second - use the corresponding collation for fields containing your data. More information than I've already provided - is in case sensitivity manual page.

score 0 · Answer 3 · answered Sep 19 '13 at 10:41

0

Make use of strtolower to Lowercase all your query and then do the search.

Something like this so it will match all.

$query=strtolower($query);
$raw_results = mysql_query("SELECT * FROM product
            WHERE (`naam` LIKE '%".$query."%') OR (`titel` LIKE '%".$query."%') OR (`druk` LIKE '%".$query."%')") or die(mysql_error());

answered Sep 19 '13 at 10:41

Shankar Narayana Damodaran

68,075
43
96
126

`mb_strtolower()` should be used instead of `strtolower`. – Glavić Sep 19 '13 at 10:47
I don't think they make much of a difference. – Shankar Narayana Damodaran Sep 19 '13 at 11:15
1

ofc they do, example: `echo strtolower('Š') . mb_strtolower('Š');` will output `Šš`. But yes, it depends on what kind of charset will OP use. – Glavić Sep 19 '13 at 11:24
the charset is latin1_bin – Koessien Sep 19 '13 at 11:59
@Glavić, +1 for the explanation. – Shankar Narayana Damodaran Sep 19 '13 at 12:06

score -3 · Answer 4 · answered Sep 19 '13 at 10:43

-3

convert all into either lowercase or upper case it will work

$raw_results = mysql_query("SELECT * FROM product
            WHERE (strtolower(naam) LIKE '%".strtolower($query)."%') OR (strtolower(titel) LIKE '%".strtolower($query)."%') OR (strtolower(druk) LIKE '%".strtolower($query)."%')") or die(mysql_error());

answered Sep 19 '13 at 10:43

Janak Prajapati

896
1
9
36

`strtolower` is not a mysql function. – PeeHaa Sep 19 '13 at 11:25

Search query, capital letters

4 Answers4