0

Generally we are doing a search operation by using like operator in sql. For example,

If user doing search text ( input string ) as "Kannan" means It's should work operation like 

Select * from users where name like <cfqueryparam value="%#url.search#%" cfsqltype="cf_sql_varchar">

The above cfm query should be like ( Select * from users where name like '%Kannan%' )

One of my end user search text ( input string ) is %%% means So the query like

Select * from users where name like '%%%%%'

It's return all the records from the user table. I don't have any name contains % But the search string (%%%) return all the records.

I've directly run the select * from users where name like '%%%%%' in my sql it's also return all records from the table.

I've go through the docs they mentioned as like '%%%' (or) like '%%%%' ( or ) if the % only in between start & end % (wildcards ) symbol without any other string mean it's return all the records from the particular table.

How I can handle this problem ? If the user search %%% then should not return any records until the name column having % value

Thanks is advance !

Kannan.P
  • 1,263
  • 7
  • 14
  • 3
    `%` is a wildcard. a clause like `'%%%'` is simply 3 wildcards. If you need need to look for the `%` (or other reserved) character, you need to enclose it (the character) in brackets (`[]`). Therefore `WHERE Column LIKE '%[%]%'` would search for values that contain the character `'%'`. – Thom A Aug 28 '19 at 10:52
  • Lamu, Thank you for your attention here. % is an user string so we can't give [] for all values. Because the user string is dynamic one. Consider my first example if the user search as 'Kannan' means then it's consider as LIKE '%[kannan]%'. it's not return any records. Hope you understand ! – Kannan.P Aug 28 '19 at 10:54
  • 3
    @Kannan.P, you will need to escape wildcard characters in the user input. Not just "%', but "_" and square brackets as well. See https://stackoverflow.com/questions/19551891/t-sql-special-characters-to-escape-for-like-operator-wildcard-search – Dan Guzman Aug 28 '19 at 11:03
  • @DanGuzman,Let's imagine if the column have value with % for example ( Hai%how ). How you use this in like operator. Could you please give your sample query ? – Kannan.P Aug 28 '19 at 11:24
  • 1
    The resultant query would need to be `LIKE '%Hai[%]how%'` or `LIKE '%' + 'Hai[%]how' + '%'` – Dan Guzman Aug 28 '19 at 11:39
  • @DanGuzman Again I want to remind one thing my input is dynamic one. So I can't use [] in specific place. As well if my column have % value mean that time I should consider that too. So I can't omit anything . – Kannan.P Aug 28 '19 at 11:42
  • @Kannan.P Mostly the search text shouldn't be only the % symbol. But the search text contains with some other alpha numeric value. for example user can using the search text 100%. So, the question is valid one. – Sathish Chelladurai Aug 28 '19 at 11:43
  • Thank you for understood Sathis. But the testing/QA time it's get failed because they test it bu using %%% value. In that time it's return all records from the particular table. So If we have % in our combination of string mean the we should consider that. I hope understood you know the logic in CFML side too – Kannan.P Aug 28 '19 at 11:45
  • @Kannan.P Just try the script with escape clause `like '%!%%' ESCAPE '!';` – Sathish Chelladurai Aug 28 '19 at 11:50
  • 2
    @Kannan.P, I understand the input is user-entered. The code will need to escape embedded wildcard characters so that those are not evaluated as wildcards in the resultant `LIKE` expression. The user does not need to do that. – Dan Guzman Aug 28 '19 at 11:56

5 Answers5

2

Escape the literal % character:

Select * from users where name like <cfqueryparam value="%#Replace(url.search,"%","\%","all")#%" cfsqltype="cf_sql_varchar">

You may try like this for search scenario. Hope, it may help to you.

Sven Eberth
  • 3,057
  • 12
  • 24
  • 29
S J kali
  • 21
  • 3
2

Change this:

Select * 
from users 
where name like <cfqueryparam value="%#url.search#%" cfsqltype="cf_sql_varchar">

to this:

<cfset searchString = "%" & replace(url.search, "%", "[%]", "all") & "%">
<cfquery name = "something">
Select * 
from users 
where name like <cfqueryparam value="#searchString#" cfsqltype="cf_sql_varchar">       
</cfquery>
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
1

In a LIKE pattern, the '%' matches any number of characters, including zero.

So, multiple '%' in a row do exactly the same thing. They match any number of characters, including zero characters.

The other wildcard, '_' behaves differently. It matches exactly one character -- not zero not two. So '__' matches exactly two. And '__%' matches a string with two or more characters.

EDIT:

If you want to search for the value explicitly, don't use like.

where instr(name, @uservalue)

Only use like if you want % and _ to be treated as wildcards. And, if you are thinking of allowing that, I would recommend regular expressions instead -- because they are more powerful and commonly used across many different systems (databases and otherwise).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • please go through my issue again. In my case %%% user input. I can write as LIKE '%userInput%' , So here my user input is %%%. It's very rare case but it's an problem too. – Kannan.P Aug 28 '19 at 10:57
  • @Kannan.P . . . What is your issue? Do you actually want to search for percent signs? – Gordon Linoff Aug 28 '19 at 13:20
  • Yes. If the column have Percent sign mean I should search , but if the column don't have any percent signs mean then it's should return o records instead of all . – Kannan.P Aug 28 '19 at 13:30
  • @Kannan.P - Take a look at Gordon's edit. If just you're searching for a *literal* anywhere within the text, just use `WHERE INSTR( column, '%') > 0`, instead of `LIKE`. Essentially, it does the same thing as `LIKE`, but doesn't treat percent symbols`%`, etc... as wildcards. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6f00f6f9bb7e040a9566845c402dd7de – SOS Aug 28 '19 at 20:41
0

At a total guess, but seems like you'll need to replace the characters in your parameter first, and then search:

SET @YourParameter = '%' + REPLACE(REPLACE(REPLACE(@YourParameter,'[','[[]'),'_','[_]'),'%','[%]') + '%';

SELECT {Your Columns}
FROM YourTable
WHERE YourColumn LIKE @YourParameter;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • @lamu, if I follow your method as replace a means, Imagine in my name column really have value %, For example ( K%nnan) means that time it's should return result. But your replace case omit the % as ( Knnan ) and return no records. I hope you understood the scenario. – Kannan.P Aug 28 '19 at 10:59
  • 1
    In your example, square bracket should be replaced first (you add some when replacing % and _). – Salman A Aug 28 '19 at 11:13
  • @Kannan.P `'K%nnan' LIKE '%[%]%'` is true, `'Kannan' LIKE '%[%]%'` is false. – Salman A Aug 28 '19 at 11:14
  • You're right, @SalmanA, I added `[` as an after thought and simply didn't consider the order. – Thom A Aug 28 '19 at 11:18
  • Why would this omit the `'%'` @Kannan.P? And it's `rn`; it's not an `m`... – Thom A Aug 28 '19 at 11:20
0

Mostly the search text shouldn't be only the % symbol. But the search text contains with some other alpha numeric value. for example user can using the search text 100%. So, the question is valid one. Here I would suggest you to use the Escape clause.

The given following SQL statement, the ESCAPE clause used to escape the character ! to negate the meaning of ‘%’ to find the string ‘100% Free’ in the column offer of the product table.

SELECT *
FROM products
WHERE offer LIKE '%100!% Free%' ESCAPE '!';

You may try with that in your search scenario. Hope, it may help to you.

Source from Using SQL LIKE with the ESCAPE clause here https://www.sqlshack.com/overview-of-the-sql-like-operator/

Sathish Chelladurai
  • 670
  • 1
  • 8
  • 23