Update:
While this is an answer to part of your question, Peter’s answer is better because it directly addresses your question: “Why use cfqueryparam when CF automatically adds protection by escaping single quotes?”. Answer: In short, because the latter does not always work. Binding variables do.
He says that in the docs he “avoids string variables in single quotation marks”, but doesn't do it “magically” wrong in the CF query tag when you wrap evaluated variables in single quotes?
Yes, most versions automatically avoid single quotes as a security measure for those who do not use cfqueryparam. However, as Scott noted, it is better to use cfqueryparam (i.e., bind variables) because they ensure that parameters are not executed as sql commands . Variables work, even when auto-escaping fails, as Peter’s answer shows .
However, sql intrusion protection is just a side effect of using bind variables. The main reason for using binding variables is performance. Bind variables, prompting databases to reuse query plans , rather than creating a new plan each time # parameter # is changed. This reduces compilation time, improving performance.
Cfqueryparam also has a number of other benefits:
- Provides data type checking (length, value, type, ...)
- Provides attributes that simplify the handling of "lists" and
null values - Performs a data type check before any sql is sent to the database, preventing useless calls to the database
Although this does not really apply to string columns, IMO is another important reason for using it - accuracy. When you pass a quoted string to the database, you rely on implicit conversion . Essentially, you leave this to the database to figure out how best to perform the comparison, and the results are not always what you expected. (Date strings are a prime example). You may end up with inaccurate results or sometimes slower queries, depending on how the database decides to execute sql. Using cfqueryparam avoids these problems by eliminating the ambiguity.
Leigh source share