6 Replies Latest reply on Jul 3, 2007 9:17 AM by Mark.Gregory

    SQL Injection and <cfqueryparam>

    Level 7
      I was told to look into <cfqueryparam> to assist in fighting sql-injection
      and it makes perfect sense, up until I thought of a different scenario...

      This tag seems great when you are dealing with numbers or text that you can
      restrict the number of characters, but what if you have a textarea that
      allows for a large amount of text to be entered? I.E. a search field for
      records that uses keywords.

      How you stop someone from entering damaging sql into an area that accepts
      this?

      Thanks for any education.

      --
      Wally Kolcz
      MyNextPet.org
      Founder / Developer
      586.871.4126


        • 1. Re: SQL Injection and &lt;cfqueryparam&gt;
          Level 7
          Ok, maybe I found an answer from the master Ben Forta. In one of his
          articles he states that primarily numeric inputs are the issue due to them
          not being surrounded by quote marks.

          "If a text value is tampered with you'll end up with tampered text, but that
          text will all be part of the core string (within quotes) passed as a value,
          and will therefore not be executed as separate statements. Numbers, on the
          other hand, are not enclosed within quotes, and so extraneous text can be
          tampered with to create an additional SQL statement."

          Can setting all numeric values requests with a <cfqueryparam> tag help in
          stopping SQL-injection?


          • 2. Re: SQL Injection and &lt;cfqueryparam&gt;
            Stressed_Simon Level 1
            By using cfqueryparam for all your sql you will protect yourself from sql injection attacks entirely and improve the performance of your sql!
            • 3. Re: SQL Injection and &lt;cfqueryparam&gt;
              Cooljj98
              quote:

              Can setting all numeric values requests with a <cfqueryparam> tag help in
              stopping SQL-injection?


              Yes, because if you use something like this <cfqueryparam cfsqltype="cf_sql_integer" value="#myvar#"> it will throw an error if the value is anything but an integer. But the best way to learn this and be sure for yourself is to practice some SQL injection with some test scripts and a test database so you can see what using <cfqueryparam> will do for you depending on what datatype you are checking for.

              CoolJJ
              • 4. Re: SQL Injection and &lt;cfqueryparam&gt;
                Level 7
                I see that this will work perfectly for numeric values since they are
                outside of quote marks, but what about if you end the string request with
                another "?


                • 5. Re: SQL Injection and &lt;cfqueryparam&gt;
                  Level 7
                  WebDev wrote:
                  It works because <cfqueryparam ....> tells the DBMS that this data is a
                  value NOT SQL. The DBMS will then never process it as SQL. When you
                  write the SQL and Values straight into the code, then the DBMS does not
                  know what is what and assumes it all must be SQL.

                  An Example...

                  <cfquery ....>
                  SELECT aField FROM aTable WHERE aField = '#aValue#'
                  </cfquery>
                  With this code, ColdFusion process the entire body of the <cfquery...>
                  tag into a string and sends that entire string to the DBMS as SQL. The
                  DBMS then processes what it was given. If somebody can modify the
                  aValue variable to change the SQL string - that is what is processed.

                  <cfquery ...>
                  SELECT aField FROM aTable WHERE aField = <cfqueryParam value="#aValue#"...>
                  </cfquery>
                  With this code ColdFusion process the SQL and the queryParam as separate
                  things. It sends the DBMS the SQL with parameters and a list of values
                  to be used in those parameters. The DBMS knows the parameters are not
                  SQL and will not process it as SQL and if the parameter contains SQL it
                  will just be used as a value and not parsed.

                  FYI... That is how <cfqueryparam...> can improve performance. By
                  knowing what parts of the SQL are variables, it can cache the SQL and
                  just use different variables when they are passed to the DBMS.

                  HTH
                  Ian
                  • 6. Re: SQL Injection and &lt;cfqueryparam&gt;
                    Mark.Gregory
                    If you or your DBAs or Sys Admins are a bit paranoid, you can provide an extra layer of defense by replacing questionable characters like {, [, < etcetera. Kind of cheesy, but effective to a point