sql script injection

If it's a SQL injection attack, use of the sa account is probably not an issue -- though to guard against other attacks it's not recommended.

Somewhere your web script (ASP, PHP, etc.) is taking user input (querystring, POST variables, etc.) and putting it right into a query.

Something like:

dim SQL
SQL = "Insert Into MyTable(Field1), Values ('" & POST("UserName") & "' "
cmd.execute SQL

What you need to do instead is parameterize.  Don't concatenate your variables directly into your string.  Someone savvy w/ input values can take advantage of that code -- and evidently has.

If this is an ASP site you can use this to filter sql injection:

http://blogs.iis.net/nazim/archive/2008/04/28/filtering-sql-injection-from-classic-asp.aspx

Gee - I have literally thousands of lines of code - hundreds of sql update statements - how can I ever catch up when I keep having to rebuild databases or reload backups?

This will be a big project. Inline statement is a big no no in database point of view.
You have to plan for this big revamp on your code.

How can they access database tables that are behind a log in?

Im not a developer, but this link will help you on basics : http://www.webconcerns.co.uk/asp/sqlqueries/sqlqueries.asp

http://msdn.microsoft.com/en-us/library/ms190669.aspx

Now send your developers on a course to learn how to do their job properly. You are already seeing the cost of not doing that.

How can I fix this?  It is asp and it was an addnew with an update, which I'm trying to change to an insert.  I get a type mismatch error on the insert statement (SQL = "insert into...)

        If strNew = "N" then
                SQL = "update " +strDB+ " set parmnm="+parmnm+",var1="+var1
        else
                SQL = "insert into " +strDB+ " (parmnm,var1) values("+parmnm+","+var1+")"
        end if          
        SQL_Rs.open SQL,SQL_Cn,1,2
        cmd.execute SQL
        SQL_Rs.close


It was (and this always moved the input field into a variable, so I'm not sure I understand how they use this to inject their javascript into the data fields) :
      If strNew = "N" then
       SQL = "SELECT * FROM " & strDB & " WHERE ID = " & iID
       SQL_Rs.open SQL,SQL_Cn,1,2
     else
       SQL = "SELECT * FROM " & strDB & " WHERE ID = 99999999"
       SQL_Rs.open SQL,SQL_Cn,1,2
       SQL_Rs.addnew
     end if
  SQL_Rs.fields("parmnm") = parmnm
  SQL_Rs.fields("var1") = var1
  SQL_Rs.update  
  SQL_Rs.close
 

Also, what are the ramifications of changing the sa password in SQL2005? I know I have to change the connect statements, but in sql2000 you had change the service start, but I don't see a service for sql2005?  

One other thing that has me confused - they are injecting the code into tables and fields that do not have forms on the web site at all or the form is behind a log in - how is that done?

Changing the SA password does not require any change to the service account because the service account is always domain, local or network - not SA. That is true of all versions of SQL Server.

Applications should have no need to use SA to connect to SQL Server. If they are, then you'll need to change the credentials used for each application whenever you change the password. Obviously that is not to be recommmended. Use integrated security or create another SQL login in place of SA.

OK - so I created a new account to use with just read and write (must have for forms) and it will not connect.  I cannot figure out why - no error, just no connection to the database.

I went through and gave permissions for each table - still not connecting.  SA has no problem connecting.  The new user has public, dbowner and then read/write and delete/insert/select/update for each table.

What happens when you try to connect via Management Studio?

Changing the login won't prevent SQL Injection. To do that you need to fix your code.

I figured it out - expired password every time on creation.

I cannot win this battle - if I give my users permission to update the data (content management), then everyone will have permission to inject their code in the data, right?

For the short term, would INSTR(parmnm,'java')  and if greater than 0, don't write/update the record work?  They are inserting javascript.

Or could you show how to apply a trigger on update that would check to see if "<script>" was in any field?

>>For the short term, would INSTR(parmnm,'java')  and if greater than

Maybe ... if you can implement it quickly it would be worth a try.

I did change the code to use instr and I changed all the users and changed the sa password - no one has hit me yet today.

ok - I changed the user,  permissions and checked for script in any updated field.  They still got in and wrote their script in every data field.  
I don't know how to create a stored procedure, but I think I can figure that out.
What I can't figure out is how to call it in VBScript with parameters.  Can you help me?

That is to call it without a user/password in the connect statement - there would be no advantage to using stored procedures if you have to give a connect statement with the user/password in it because that's apparently all they need, since I'm checking any input for "script" and they are still injecting it.

How can this work without a connection statement?
Don't I need:
Set cn = Server.CreateObject("ADODB.Connection")
   cn.Open "data source name", "userid", "password"
   Set cmd = Server.CreateObject("ADODB.Command")
   Set cmd.ActiveConnection = cn
which, if I put here, seems to give the hackers all they need.
Remember, I'm checking for instr("script") before any updating/inserting is done and they're still injecting script.  I'm also changing the user/pwd every day and I'm not using SA.  They're updating tables and fields that are not in any form on the site.  Most are behind a log in.  I've isolated and secured the connect script and I'm using an include now.  I've changed the SA password.  
They're hitting me at least once a day, even though we don't keep credit cards on our server.  
They do not appear to be using the code - they appear to be getting the user/pwd and just running through every table/field and injecting script.
Please help me stop them!

>>Don't I need:

Yes.  I didn't put the entire code necessary -- just the part about which I thought you were unsure.

Creating a connection object does not make you vulnerable.  Using their input directly does.

OK - Do I have to set the @ variables somewhere?
Here's what I've got (with errors):


 For i = 0 to 27
       req=request.form(arrResults(i,0))
        VarX="Var"&i
        If VarX = "Var0" then
         VarX = "ParmNm"
        End If
        If VarX = "ParmNm" then ParmNm = req
        If VarX = "Var1" then Var1 = req
        If VarX = "Var2" then Var2 = req
        If VarX = "Var3" then Var3 = req
 Next
 
     Set SQL_Cn = server.createobject("ADODB.Connection")
     SQL_Cn.Provider = "sqloledb"
     SQL_Cn.Properties("Data Source").Value = "10.10.1.12, 1045"
     SQL_Cn.Properties("Initial Catalog").Value = "Tablename"
     SQL_Cn.Properties("User ID").Value = "user"
     SQL_Cn.Properties("Password").Value = "password"    
     SQL_Cn.open
     Set SQL_Rs = Server.CreateObject("ADODB.Recordset")
      SQL_RS.ActiveConnection = SQL_Cn

 If strNew = "N" then
                SQL = "update " +strDB+ " set parmnm=@p1 ,var1=@p2,var2=@p3,var3=@p4"
 else
                SQL = "insert into " +strDB+ " (parmnm,var1,var2,var3) values(@p1,@p2,@p3,@p4"
 end if  
 Set     cmd = Server.CreateObject ("ADODB.Command")
         cmd.ActiveConnection = SQL_cn
         'cmd.CommandText = "updtcust"
         cmd.CommandText = SQL
         cmd.CommandType = adCmdStoredProc
 
         cmd.Parameters.Append cmd.CreateParameter("@parmnm", adVarWChar, 255, adInput, parmnm)
         cmd.Parameters.Append cmd.CreateParameter("@var1", adVarWChar, 255, adInput, var1)
         cmd.Parameters.Append cmd.CreateParameter("@var2", adVarWChar, 255, adInput, var2)
         cmd.Parameters.Append cmd.CreateParameter("@var3", adVarWChar, 255, adInput, var3)


It gets an error that Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another - on cmd.CommandType=adCmdStoredProc.
I've tried using "SQL" and the "updtcust" stored procedure I created, but it is just for update at this point.

USE [Promotions]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[UpdtCust]
      -- Add the parameters for the stored procedure here
AS
BEGIN
       DECLARE @p1 varchar(255)
       DECLARE @p2 varchar(255)
       DECLARE @p3 varchar(255)
       DECLARE @p4 varchar(255)
       update dbo.db6l1p1 set parmnm=@p1,var1=@p2,var2=@p3,var3=@p4
END
 

Same error and they are all varchar.  The ID field is not included anywhere in this - does it need to be?
Also, do I need to define/set the @ variables before this code?

On which line are you getting the error?

You are setting your variables.  You are setting the @ variables w/ the CreateParameter lines.

For varchar types, use adVarChar, not adVarWChar.

I figured the advarwchar out, but it's getting the error on the cmd.CommandType = adCmdText.
It's possible that some of the variables are not used - this is a generic program.
Do I need to set a default if null or non-existent?

cmd.ActiveConnection = SQL_cn

should be

set cmd.ActiveConnection = SQL_cn

New Code:

Parmnm=""
Var1=""
Var2=""
Var3=""

 For i = 0 to 27
       req=request.form(arrResults(i,0))
        VarX="Var"&i
        If VarX = "Var0" then
         VarX = "ParmNm"
        End If
        If VarX = "ParmNm" then ParmNm = req
        If VarX = "Var1" then Var1 = req
        If VarX = "Var2" then Var2 = req
        If VarX = "Var3" then Var3 = req
 Next
 
     Set SQL_Cn = server.createobject("ADODB.Connection")
     SQL_Cn.Provider = "sqloledb"
     SQL_Cn.Properties("Data Source").Value = "10.10.1.12, 1045"
     SQL_Cn.Properties("Initial Catalog").Value = "Tablename"
     SQL_Cn.Properties("User ID").Value = "user"
     SQL_Cn.Properties("Password").Value = "password"    
     SQL_Cn.open
     SQL_RS.ActiveConnection = SQL_Cn
 If strNew = "N" then
         SQL = "update " +strDB+ " set parmnm=@p1 ,var1=@p2,var2=@p3,var3=@p4"
         Set     cmd = Server.CreateObject ("ADODB.Command")
         cmd.ActiveConnection = SQL_cn
         cmd.CommandText = "updtcust"
      else
         SQL = "insert into " +strDB+ " (parmnm,var1,var2,var3)"
         Set     cmd = Server.CreateObject ("ADODB.Command")
         cmd.ActiveConnection = SQL_cn
         cmd.CommandText = "insertcust"
    end if        

Set     cmd = Server.CreateObject ("ADODB.Command")
          cmd.ActiveConnection = SQL_cn
          cmd.CommandText = SQL
          cmd.CommandType = adCmdText

         cmd.Parameters.Append cmd.CreateParameter("@p1", adVarChar,adInput,255, parmnm)
         cmd.Parameters.Append cmd.CreateParameter("@p2", adVarChar, adInput, 255, var1)
         cmd.Parameters.Append cmd.CreateParameter("@p3", adVarChar, adInput, 255, var2)
         cmd.Parameters.Append cmd.CreateParameter("@p4", adVarChar, adInput, 255, var3)


ADODB.Command error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another on
          cmd.CommandType = adCmdText
All variables are varchar.

Someone please help - I just go hit again.

I got it - here it is for future reference for those who can't get this in ASP:
Parmnm=""
Var1=""
Var2=""
Var3=""

 For i = 0 to 27
       req=request.form(arrResults(i,0))
        VarX="Var"&i
        If VarX = "Var0" then
         VarX = "ParmNm"
        End If
        If VarX = "ParmNm" then ParmNm = req
        If VarX = "Var1" then Var1 = req
        If VarX = "Var2" then Var2 = req
        If VarX = "Var3" then Var3 = req
 Next
 
     Set SQL_Cn = server.createobject("ADODB.Connection")
     SQL_Cn.Provider = "sqloledb"
     SQL_Cn.Properties("Data Source").Value = "10.10.1.12, 1045"
     SQL_Cn.Properties("Initial Catalog").Value = "Tablename"
     SQL_Cn.Properties("User ID").Value = "user"
     SQL_Cn.Properties("Password").Value = "password"    
     SQL_Cn.open
     SQL_RS.ActiveConnection = SQL_Cn
     Set     cmd = Server.CreateObject ("ADODB.Command")
               cmd.ActiveConnection = SQL_cn
     If strNew = "N" then
               cmd.CommandText = "updtcust"
      else
               cmd.CommandText = "insertcust"
     end if    
'200 = adVarChar - must look up other types    
               cmd.CommandType = adCmdStoredProc
               cmd.Parameters.Append cmd.CreateParameter("@p1", 200,&H0001, 80, parmnm)
               cmd.Parameters.Append cmd.CreateParameter("@p2", 200,&H0001, 80, var1)
               cmd.Parameters.Append cmd.CreateParameter("@p3", 200,&H0001, 80, var2)
               cmd.Parameters.Append cmd.CreateParameter("@p4", 200,&H0001, 80, var3)
               cmd.execute
              set cmd=nothing

The insertcust stored procedure:

USE [Promotions]
GO
/****** Object:  StoredProcedure [dbo].[insertcust]    Script Date: 05/28/2008 17:06:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[insertcust]

       @p1 varchar(80),
       @p2 varchar(80),
       @p3 varchar(80),
       @p4 varchar(80)
   
AS
BEGIN
       insert into dbo.db6l1p1 (parmnm,var1,var2,var3) values(@p1,@p2,@p3,@p4)
END