7 Replies Latest reply on Jun 21, 2006 8:54 AM by drforbin1970

    Preventing SQL Injection

    leesiulung
      I'm looking for ways to prevent SQL injection attack and happen to find an excellent article by Ben Forta:

      http://br.sys-con.com/read/165921.htm

      However, he does not mention how to prevent fields that contains strings, such as names. Doing an IsValid("string", fieldnames) does not prevent someone putting in SQL into a field when validating the data.

      Bottom line is: How do one validate fields that contains strings to prevent someone from injecting sql?
        • 1. Re: Preventing SQL Injection
          Level 7
          leesiulung wrote:
          > I'm looking for ways to prevent SQL injection attack and happen to find an
          > excellent article by Ben Forta:
          >
          > http://br.sys-con.com/read/165921.htm
          >
          > However, he does not mention how to prevent fields that contains strings, such
          > as names. Doing an IsValid("string", fieldnames) does not prevent someone
          > putting in SQL into a field when validating the data.
          >
          > Bottom line is: How do one validate fields that contains strings to prevent
          > someone from injecting sql?
          >

          <CFqueryparam ...> This is one of the biggest reasons for using this
          tag, to prevent SQL injection. By doing this all your query values are
          turned into parameter variables. Thus any passed in strings are not put
          directly into your SQL string, but passed seperatly as identified and
          typed pieces of data. Thus if you tell your DBMS that a value is a
          string, that database says this is a string and I will put all its
          characters into the field.

          Thus if anybody tries to pass a SQL injection string to your query, all
          that happens is you save all the characters into your database, or as
          many as the field allows.

          Use <CFQueryParam...> always, it is your friend.
          • 2. Re: Preventing SQL Injection
            coderWil
            I would use <cfqueryparam ...>

            This tag sends a representation to the database that the variable inside it is a variable value. If an injection is attempted, the 'additional code' added to the query will be treated as a variable rather than sql code.

            eg.

            select * from customers
            where username = <cfqueryparam value="#form.username#">
            and password = <cfqueryparam value="#form.password#">

            This would stop a person who sent a password value of say:
            wrong' or 1 = 1 or username <> '

            So instead of messing with the sql, the query is looking for a password that exactly matches the above line of text.

            Please note 2 things.
            1. When using cfqueryparam, do not surround it with quotes to designate a char value.
            2. You will use all ability to cache a query by using this tag

            Hope this helps

            • 3. Re: Preventing SQL Injection
              drforbin1970 Level 1
              CFQUERYPARAM is indeed a must. Don't forget to use CFTRY/CFCATCH everywhere. Besides SQL attacks, if you don't validate your data used by CF, your code could 'break', thus dumping valuable info in an error message. Make sure you validate all user input and use CFTRY/CFCATCH.

              Test inputted data for what is allowed, instead of for what is NOT allowed.
              • 4. Re: Preventing SQL Injection
                Freffie
                Hi. Does anyone have an experience using CFQUERYPARAM when your query is stored in a string variable?
                • 5. Re: Preventing SQL Injection
                  leesiulung Level 1
                  drforbin1970, can you give me an example of how to use this cftry/catch with the cfquery?

                  Thanks,
                  • 6. Re: Preventing SQL Injection
                    Rushglen2
                    SQL injection comes in many forms, here's a good read about myspace.com

                    but most of all, samy is my hero

                    and how he did it (the code):
                    http://namb.la/popular/tech.html

                    Andrew
                    • 7. Re: Preventing SQL Injection
                      drforbin1970 Level 1
                      Check documentation for CFTRY/CFCATCH. More info there than I can give you.