Wildcard, '%', in LIKE request for cfscript ColdFusion request?

Question

Can I use a wildcard in a SQL LIKE statement in a cfscript ColdFusion query?

An example that does not work:

local.q = new Query(); local.q.setDatasource(variables.dsn); local.q.addParam(name='lastname', value='%' & arguments.lastname, cfsqltype="cf_sql_varchar"); local.qString = 'SELECT name FROM users WHERE lastname LIKE :lastname'; local.q.setSQL(local.qString); local.result = local.q.execute().getResult(); 

I also tried these that did not work:

 local.qString = 'SELECT name FROM users WHERE lastname LIKE %:lastname'; local.qString = "SELECT name FROM users WHERE lastname LIKE '%:lastname'"; 

UPDATE:

I am using MS SQL Server 2008.

The query works fine in Mgmt Studio SQL Server ... I think it has something to do with how to format the query in cfscript tags?

Answer 1

Yes it is possible. You set it in the parameter, which is correct. I am not sure why it does not work with you.

I did the following and it worked.

 var qryArgsCol = {}; qryArgsCol.datasource = variables.datasource; qryArgsCol.SQL = " SELECT ID FROM Users WHERE LastName LIKE :searchStringParam "; var qryGetID = new query(argumentCollection=qryArgsCol); qryGetID.addParam(name="searchStringParam", value="%" & searchString, cfsqltype="cf_sql_varchar"); qryGetIDResult = qryGetID.execute().getResult(); 

Answer 2

I would suggest using the CFQuery tag instead of trying to run queries in CFScript . Unless you REALLY know what you are doing. I’m talking about this because the CFQuery tag has built-in functions that not only simplify the creation of queries for you, but can also protect you from unexpected attacks (type of SQL injection). For example, when using CFQuery it will automatically remove single quotes for you to insert things like 'well isn't that a mess' , it will not explode on you. You can also use the CFQueryParam tag to further combat SQL injection attacks. Although you can use CFQueryParam functionality within CFScript , this is not so straightforward (at least not for me).

See this Ben Nadel blog post about this.

So, in CFQuery tags your query will look something like this:

 <cfquery name="myQuery" datasource="#variables.dsn#"> SELECT name FROM users WHERE lastname LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%:#arguments.lastname#" maxlength="256" /> </cfquery> 

Answer 3

Depending on the dbms used, these single and double quotes may be interpreted when the sql statement is run. What dbms are you using? Now your statement does not select the value in the variable, but for any user whose name is "lastname". It should be something like:

  lastname like '%#lastname#' 

Answer 4

There was a response from Adam Cameron, which apparently had been removed by an excessive mod.

Instead of repeating what he says, I just copied and pasted (with emphasis on key parts):

---

To clarify that the syntax you tried in the first example works . This is the right approach. To clarify / explain:

The <cfquery> version of your example will look like this:

 <cfqueryparam value="%foo"> 

So, in the version of the function, the parameter will be ? or :paramName , and the parameter value will be "%foo" .

% is part of the parameter value, not the SQL string.

So, if you do not use “does not work,” it will help if you post an error message or something else that makes you think that it is not working (what is your expectation and what are the actual results). Then we can deal with the actual cause of your problem, which, in my opinion, is not what you think.

Does the query work like <cfquery> ?

Answer 5

Just remember that you ultimately need to see what CF gives the database server. In this case, you can try this layout to get closer and find the same error in SSMS by messing up the quotes / value in the param declaration:

  declare @param1 varchar(max) = '%Eisenlohr'; SELECT name FROM users WHERE lastname LIKE @param1 

Answer 6

I ran into the same problem as the original poster, where it “didn’t work,” and I did not get any results from the query request. The problem for me is that pattern matching is case sensitive.

  local.q = new Query(); local.q.setDatasource(variables.dsn); local.q.addParam(name='lastname', value='%' & LCase(arguments.lastname), cfsqltype="cf_sql_varchar"); local.qString = 'SELECT name FROM users WHERE LOWER(lastname) LIKE :lastname'; local.q.setSQL(local.qString); local.result = local.q.execute().getResult(); 

So, I made the input argument lowercase and make sure the SQL compare field was also lowercase and it worked.

Answer 7

Use this.

  local.q = new Query(); local.q.setDatasource(variables.dsn); local.q.addParam(name="lastname", cfsqltype="cf_sql_varchar",value='%ARGUMENTS.lastname' ); local.qString = 'SELECT name FROM users WHERE lastname LIKE :lastname'; local.q.setSQL(local.qString); local.result = local.q.execute().getResult();