0

I want to keep a history of sql commands that have been run from an application in ColdFusion, but I keep getting this error:

The name 'select * from sql_history' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.

Here is my code

<cfquery name="history" datasource="#ds#">
    INSERT INTO sql_history VALUES
    ("#form.sql#")
</cfquery>

My sql_history table is just an int 'id' and text 'sql'.

Is there a way to escape the query or something so that it just goes in as a string?

Travis Heeter
  • 13,002
  • 13
  • 87
  • 129
  • 1
    Not related to your question, but a datetime field with a default value of getdate() might come in handy. – Dan Bracuk Oct 25 '13 at 16:14

1 Answers1

4

Use a query parameter. It will solve almost all your problems.

<cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.sql#">

Matt Busche
  • 14,216
  • 5
  • 36
  • 61
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • 1
    So ? I thought a major reason for using that was to prevent sql injections? – Travis Heeter Oct 25 '13 at 16:06
  • 1
    query parameters do many good things for you. Among them is escaping quotes in your string. On occasion they make queries run faster as well. And yes, your guess at the code is correct. – Dan Bracuk Oct 25 '13 at 16:11
  • 3
    @TravisHeeter - It works because cfqueryparam uses bind variables. Bind variables prevent parameter *values* from being executed as SQL commands. As a result this provides protection against sql injection. Malicious commands do nothing because they are never executed. (BTW, [the primary reason for using bind variables is to improve performance](http://stackoverflow.com/questions/17574276/how-can-cfqueryparam-affect-performance-for-constants-and-null-values/17582859#17582859). The sql injection protection is just a side benefit, albeit an important one). – Leigh Oct 25 '13 at 16:15
  • Actually, the comment about malicious commands never being executed does not apply to javascript or html, but that's a different story. – Dan Bracuk Oct 25 '13 at 16:18
  • 2
    @DanBracuk - Yep, but those are different attack vectors. *SQL* injection typically refers only to attacks executed via sql commands. But as you say, there is a host of other threats out there beyond simple sql attacks. – Leigh Oct 25 '13 at 16:24