9 Replies Latest reply on Mar 21, 2008 9:50 AM by Oğuz Demirkapı

    trying to secure code from sql injection

    vamike999
      ok so someone is able to add tables into my database. I know i have a whole. I have been correcting all data updates and inserts example

      <cfqueryparam CFSQLType = "CF_SQL_VARCHAR" value="#chars#">
      <cfqueryparam value="#numbs#">

      bow how can i secure a text field

      example data for my text field being added

      '#Replace("#TextData#", Chr(10), "<br>", "ALL")#'

      is there a way to also use fqueryparam on this?

      I am also getting errors why trying to use this on the date

      this was my date input before
      #CreateODBCDateTime(Event_SDate)#
      this was after
      <cfqueryparam CFSQLType = "CF_SQL_DATE" value="#CreateODBCDateTime(Event_SDate)#">

      i get this message

      ODBC Error Code = S1C00 (Driver not capable)

      I am using MS windows 2008 and MS SQL server 2000

        • 1. Re: trying to secure code from sql injection
          Level 7
          Yes, a text area is nothing special to a database. It is just text
          being stored into a text field and a text version of <cfqueryparam....>
          will preform it's parameterization magic on it. This will prevent all
          forms of SQL injection.

          The trick is that by telling the DBMS that a value is a parameter the
          database does not even look at it as SQL code but only as text and will
          just happily store it into the database.

          Without <cfqueryparam...>, all the text is SQL and any variables that
          contain SQL code is include in the overall SQL statement and run by the
          database.
          • 2. Re: trying to secure code from sql injection
            vamike999 Level 1
            anyway you can give me a quick example of how i would then add

            #Replace("#TextData#", Chr(10), "<br>", "ALL")#

            or

            #CreateODBCDateTime(Event_SDate)#


            into a database while securing it from hacks?
            • 3. Re: trying to secure code from sql injection
              vamike999 Level 1
              oh wait i just tried this and it worked for the long text

              <CFSET Event_Description = "#Replace("#longtext#", Chr(10), "<br>", "ALL")#">
              <cfqueryparam CFSQLType = "CF_SQL_VARCHAR" value="#longtext#">

              and it worked so now i am just confused on how to get this one into the database saftly

              #CreateODBCDateTime(Event_SDate)#
              • 4. Re: trying to secure code from sql injection
                Level 7
                vamike999 wrote:
                > anyway you can give me a quick example of how i would then add
                >
                > #Replace("#TextData#", Chr(10), "<br>", "ALL")#
                >
                > or
                >
                > #CreateODBCDateTime(Event_SDate)#
                >
                >
                > into a database while securing it from hacks?
                >

                <cfqueryparam value='#Replace("#TextData#", Chr(10), "<br>", "ALL")#'
                CFSQLType='CF_SQL_VARCHAR'>

                AND

                <cfqueryparam value='#CreateODBCDateTime(Event_SDate)#;
                CFSQLType='cf_sql_timestamp'>

                Be aware that there are 'cf_sql_time' and 'cf_sql_date' options as well
                depending on exactly what data you want to put into the database.
                • 5. Re: trying to secure code from sql injection
                  vamike999 Level 1
                  that worked very good. Thanks so much :-)
                  • 6. trying to secure code from sql injection
                    sean69 Level 1
                    Funny been working on exactly the same thing today,... specifically for script injection/remote command execution....
                    here is what I did:

                    1] Called from the application.cfm
                    <cfinclude template="#application.ServerRoot#/cfmx/sanitize.cfm">
                    <!--- xss protection/input sanitisation --->
                    <cfscript>sanitize();</cfscript>


                    2] the function.
                    <cfscript>

                    function sanitize() {
                    // This function detects and prevents sql/script injection type attacks and remote command execution
                    // we just need to clean up the regex statements
                    attack = 0;
                    email = '';

                    if (IsDefined('form')) {
                    for (key in form) {
                    if (ReFindNoCase('<script',form[key],'1','false') gt 0 ){attack=1;email = email&"<br />FORM STRING FOUND";}
                    }

                    if (ReFindNoCase('script',cgi.QUERY_STRING,'1','false') gt 0 ){attack=1;email = email&"<br />ILLEGAL URL STRING FOUND";}
                    }
                    if (attack eq 1) {
                    //populate vars
                    email = email&"<br />";
                    email = email&"<br />";
                    email = email&"<br />cgi.http_referer = "&cgi.http_referer;
                    email = email&"<br />cgi.http_user_agent = "&cgi.http_user_agent;
                    email = email&"<br />cgi.path_info = "&cgi.path_info;
                    email = email&"<br />cgi.path_translated = "&cgi.path_translated;
                    email = email&"<br />cgi.query_string = "&cgi.query_string;
                    email = email&"<br />cgi.remote_Addr = "&cgi.remote_Addr;
                    email = email&"<br />cgi.remote_host = "&cgi.remote_host;
                    email = email&"<br />cgi.remote_user = "&cgi.remote_user;
                    email = email&"<br />cgi.request_method = "&cgi.request_method;

                    //send mail
                    cfmail("application.adminmail","A possible web attack has been detected",email,"html");
                    //getPageContext().forward("/denied.cfm");
                    cflocation("/denied.htm","no");
                    }
                    }
                    </cfscript>


                    - yep, it's not finished, the regex's need to be written....
                    and yep every form, every request parses through = ++ overhead,...

                    but it works..

                    and it should be easy to add some rewrite rules to form values:
                    REReplaceNoCase(tmp, "(</?(APPLET|EMBED|FRAME|FRAMESET|IFRAME|ILAYER|LAYER|META|OBJECT|PARAM|SERVER)[^>]*>)", "", "ALL");





                    -regards
                    -sean


                    • 8. Re: trying to secure code from sql injection
                      Javagene Level 1
                      there is UDF for striptext