?
Solved

CFQUERYPARAM or not

Posted on 2011-09-16
6
Medium Priority
?
379 Views
Last Modified: 2013-12-24
for the query below should I be using CFQUERYPARAM?

The ClientStatusID will not be a variable. it will always be as shown.

SELECT     m.IMailID, m.ClientID, m.EnvelopeImagePath, m.EnvelopeImageStatus, m.EnvelopeImageName, u.UserID, u.FirstName, u.LastName, u.UserEmail,
                      m.EnvelopeAlertDate ,(select count(*) from imail.tblIMail where ClientID = u.ClientID) as documentCount, c.ClientStatusID
FROM         imail.tblIMail AS m INNER JOIN
                      imail.tblClients AS c ON m.ClientID = c.ClientID INNER JOIN
                      imail.tblUsers AS u ON c.ClientID = u.ClientID
WHERE  (c.ClientStatusID = 1 or c.ClientStatusID = 2)
AND NOT EXists (SELECT act.UserID
                   FROM imail.tbliMailActions act
                   where act.UserID  = u.UserID
                              and act.IMailID = m.IMailID)
0
Comment
Question by:Shawn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 36552103
well I would be using <cfqueryparam to prevent the sql injection and security purposes

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 36552145
No.  

You only need to use cfqueryparam when the query uses variable or user supplied input. If neither of those are true, you don't need cfqueryparam.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36552167
>> WHERE  (c.ClientStatusID = 1 or c.ClientStatusID = 2)

Having absolutely nothing to do with your question ;-) you could increase readability by rewriting it as:

     WHERE  c.ClientStatusID IN (1,2)
0
Monthly Recap

May was a big month for new releases from Linux Academy! Take a look at what our team built recently in our blog. You can access the newest releases from our blog.

 
LVL 1

Author Closing Comment

by:Shawn
ID: 36552231
thanks agx. that's what i thought but wasn't sure.

also thx for the query pointer. changed and is now easier to follow :-)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 36552270
The primary reasons for using it are
A) performance/caching
B) defense against sql injection
C) data type checking

I used to do it myself, but then discovered there's no benefit with constants.
A) If the values don't change the db will cache the query plan without cfqueryparam's help
B) Since they're not user supplied, there's nothing to defend against. Unless it's yourself. But then you've got bigger problems than cfqueryparam can fix.
C) Doesn't apply with constants
0
 
LVL 1

Author Comment

by:Shawn
ID: 36552311
good set of rules. I shouldn't have any more doubts. :)
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question