Solved

CFQUERYPARAM or not

Posted on 2011-09-16
6
372 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 500 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

724 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