How to prevent SQL Injection with queryService.addParam and use of %?

dev google-group-archive
1

Hi,

How do I use the “term”-Variable combined with the suffix % to prevent sql
injection with the queryService.addParam together?

queryService.setSQL("
SELECT *
FROM test
WHERE test.name LIKE ‘#arguments.term#%’

    ");

queryService.addParam(name=“term”,value=“#arguments.term#”,cfsqltype=“CF_SQL_VARCHAR”);

Regards
Thorsten

2

Hi,

How do I use the “term”-Variable combined with the suffix % to prevent sql
injection with the queryService.addParam together?

queryService.setSQL("
SELECT *
FROM test
WHERE test.name LIKE ‘#arguments.term#%’

    ");

queryService.addParam(name=“term”,value=“#arguments.term#”,cfsqltype=“CF_SQL_VARCHAR”);

Regards
Thorsten

Try these,

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE :term || ‘%’”);

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE ‘%’ || :term
|| ‘%’”);

or

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE ? || ‘%’”);

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE ‘%’ || ? ||
‘%’”);

I think you can leave the hash tags and CF_SQL out?

queryService.addParam(name=“term”,value=“arguments.term”,cfsqltype=“varchar”);

good luck.On Friday, April 8, 2016 at 9:40:23 AM UTC-5, thorste...@googlemail.com wrote:

3

Hi,

How do I use the “term”-Variable combined with the suffix % to prevent
sql injection with the queryService.addParam together?

queryService.setSQL("
SELECT *
FROM test
WHERE test.name LIKE ‘#arguments.term#%’

    ");

queryService.addParam(name=“term”,value=“#arguments.term#”,cfsqltype=“CF_SQL_VARCHAR”);

Regards
Thorsten

Try these,

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE :term ||
‘%’”);

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE ‘%’ || :term
|| ‘%’”);

or

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE ? || ‘%’”);

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE ‘%’ || ? ||
‘%’”);

I think you can leave the hash tags and CF_SQL out?

queryService.addParam(name=“term”,value=“arguments.term”,cfsqltype=“varchar”);

good luck.

Hi,

How do I use the “term”-Variable combined with the suffix % to prevent
sql injection with the queryService.addParam together?

queryService.setSQL("
SELECT *
FROM test
WHERE test.name LIKE ‘#arguments.term#%’

    ");

queryService.addParam(name=“term”,value=“#arguments.term#”,cfsqltype=“CF_SQL_VARCHAR”);

Regards
Thorsten

Try these,

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE :term ||
‘%’”);

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE ‘%’ || :term
|| ‘%’”);

or

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE ? || ‘%’”);

queryService.setSQL(“SELECT * FROM test WHERE test.name LIKE ‘%’ || ? ||
‘%’”);

I think you can leave the hash tags and CF_SQL out?

queryService.addParam(name=“term”,value=“arguments.term”,cfsqltype=“varchar”);

good luck.

Forgot to mention that maybe the addParam and argument should have
different names?On Friday, April 8, 2016 at 10:45:02 PM UTC-5, mark wrote:

On Friday, April 8, 2016 at 9:40:23 AM UTC-5, thorste...@googlemail.com wrote:
On Friday, April 8, 2016 at 10:45:02 PM UTC-5, mark wrote:
On Friday, April 8, 2016 at 9:40:23 AM UTC-5, thorste...@googlemail.com wrote:

4

In this code, you are passing in your search term and then
concatenating the wildcard in SQL. Mark has shown you the proper
syntax to do so, but I think it is better to concatenate the search
term and the wildcard in CFML before passing it in to SQL. The reason
is that the syntax of concatenation is one of those things that depend
on the database engine. What Mark showed is the SQL standard way of
doing concatenation, but many databases require different syntax.

So what I would do is concatenate in CFML:
queryService.setSQL("
SELECT *
FROM test
WHERE test.name LIKE :term
“);
queryService.addParam(
name=“term”
,value=”#arguments.term#%"
,cfsqltype=“CF_SQL_VARCHAR”
);

JochemOn Fri, Apr 8, 2016 at 4:40 PM, thorsteneilers wrote:

queryService.setSQL("
SELECT *
FROM test
WHERE test.name LIKE ‘#arguments.term#%’
");

queryService.addParam(name=“term”,value=“#arguments.term#”,cfsqltype=“CF_SQL_VARCHAR”);

–
Jochem van Dieten
http://jochem.vandieten.net/

5

Thanks for your input. I will try the different suggestions.

“I think you can leave the hash tags and CF_SQL out?”
queryService.addParam(name=“term”,value=“arguments.term”,cfsqltype=“varchar”);

It’s possible to leave the hash tags out, but only if you leave the double
quotes out too like
queryService.addParam(name=“term”,value=arguments.term,cfsqltype=“VARCHAR”);

It does work without CF_SQL too.

6

queryService.setSQL("
SELECT *
FROM test
WHERE test.name LIKE :term
“);
queryService.addParam(name=“term”,value=”#arguments.term#%",cfsqltype=“CF_SQL_VARCHAR”);

Also works.On Friday, April 8, 2016 at 10:40:23 AM UTC-4, thorste...@googlemail.com wrote:

Hi,

How do I use the “term”-Variable combined with the suffix % to prevent sql
injection with the queryService.addParam together?

queryService.setSQL("
SELECT *
FROM test
WHERE test.name LIKE ‘#arguments.term#%’

    ");

queryService.addParam(name=“term”,value=“#arguments.term#”,cfsqltype=“CF_SQL_VARCHAR”);

Regards
Thorsten