cfqueryparam issues

Question

I am updating queries to use the cfqueryparam after I was shown all the advantages to using cfqueryparam's. However I have now run into an error that I have not crossed before and not sure how to troubleshoot or where to look for the issue. I am guessing that it is a syntax issue. Here is the error-

Error Executing Database Query.[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '@P1'. The specific sequence of files included or processed is: C:\inetpub\wwwroot\cfleadsource\admin\MultipleAccountReassign_new.cfm, line: 170

Here is my query. As you can see the old query is commented out and has been replaced with the new query. It should be noted that the old query worked without issues and this is the first query on the page that has been altered. For the life of me I can not determine what is incorrect. Thanks for the help.

<cfquery name="GetAccounts" datasource="#dbConn#">
        <!--- select top #callNum# * from contact where mar in (select mar from marselect where userid = #oUID# and mar not like '%branch%') order by newid() --->
        select top  <cfqueryparam value= "#callNum#" CFSQLType="CF_SQL_INTEGER"> * from contact where
         mar in (select mar from marselect where userid = <cfqueryparam value= "#oUID#" CFSQLType="CF_SQL_INTEGER"> and mar not like '%branch%') order by newid()
    </cfquery>

The other possibility is to use `callNum` in the `maxrows` attribute of ``. Then you need not worry about possible SQL injection on that variable. The caveat is that query caching ignores `maxrows` (even if you change the value, the cached query doesn't change and still returns the previous # of rows) at least as of CF 9 -- I don't know whether that was fixed in CF 10 or 11: `` — David Faber, Feb 18 '15 at 14:54
@DavidFaber - It is better to use database commands to limit the rows. Some implementations of maxrows (not sure which versions) would retrieve all records and discard the excess in CF, which wasted resources unnecessarily. That may have been fixed in later versions, but using database commands ensures you know exactly what you are getting. If sql injection is a concern, you can always use val() and/or int() in lieu of cfqueryparam (if not available). — Leigh, Feb 18 '15 at 15:50

score 2 · Accepted Answer · edited May 23 '17 at 12:06

2

select top cannot use <cfqueryparam> for top rows without brackets.

You may try adding a bracket and see if it works: MS SQL Exception: Incorrect syntax near '@P0'

edited May 23 '17 at 12:06

Community

1
1

answered Feb 17 '15 at 20:55

Henry

32,689
19
120
221

That was it. Duh. Cfqueryparam can only be used for the limiting of a query. I was going blind trying to figure it out. I would give you reps and all the goodies but unfortunately I can not yet. Thanks again @henry – Mark Hart Feb 17 '15 at 21:01
4

*select top cannot use * No, it *can* be used with TOP in SQL Server 2005+. Just add parenthesis ie `SELECT TOP ( ) ...` (**Edit**) Darn! You have got start marking your edits ;-) That link was not there when I posted my comment, lol. – Leigh Feb 17 '15 at 21:07

cfqueryparam issues

1 Answers1