Solved

Storing dynamic SQL on a table field and preventing SQL injection

Posted on 2013-05-15
9
352 Views
Last Modified: 2013-05-21
I need to store SQL queries on a table. These will be awaiting approval and will then be executed once this happens.
Table:
CREATE TABLE [dbo].[tblAlterEmpregado](
	[FK_UserID] [uniqueidentifier] NOT NULL,
	[FK_ID_intEmpresa] [int] NOT NULL,
	[FK_ID_intFuncionario] [int] NOT NULL,
	[datDataInsercao] [datetime] NOT NULL,
	[strInsertSQL] [nvarchar](max) NOT NULL,
	[strUpdateSQL] [nvarchar](max) NOT NULL,
	[strSelectSQL] [nvarchar](max) NOT NULL,
	[strDeleteSQL] [nvarchar](max) NOT NULL,
	[strDesc] [varchar](max) NOT NULL,
	[strAccao] [char](1) NOT NULL,
	[strPagina] [char](1) NOT NULL
) ON [PRIMARY]

Open in new window

I then use this code on ASP.Net to store the query:
              Dim sqlComm2 As New SqlCommand("INSERT INTO tblAlterEmpregado " & _
                                             "SELECT UserID,@Emp,@Num,GETDATE(),@iSQL,@uSQL,@sSQL,@dSQL,@sDesc,'C','D' " & _
                                             "FROM aspnet_users " & _
                                             "WHERE UserName=@sUser", _
                                             sConn)
              sqlComm2.Parameters.Add("@Emp", SqlDbType.Int).Value = SQLUtils.MyEmp
              sqlComm2.Parameters.Add("@Num", SqlDbType.Int).Value = txt_psa_num_empregado.Text
              sqlComm2.Parameters.Add("@iSQL", SqlDbType.NVarChar).Value = "INSERT INTO tblDespesas " & _
                                                                            "SELECT " & SQLUtils.MyEmp & "," & txt_psa_num_empregado.Text & ",'" & _
                                                                                    Format(dIni, "yyyyMMdd") & "',ID_intTipoDespesa," & CType(e.Item.FindControl("txt_Qt"), TextBox).Text & _
                                                                            " FROM tblTiposDespesa " & _
                                                                            "WHERE FK_ID_intEmpresa=" & SQLUtils.MyEmp & _
                                                                                  " AND strTipoDespesa='" & CType(e.Item.FindControl("ddl_Tipo"), DropDownList).SelectedItem.Text & "'"
              sqlComm2.Parameters.Add("@uSQL", SqlDbType.NVarChar).Value = "UPDATE tblDespesas " & _
                                                                            "SET dblValorDespesa=" & CType(e.Item.FindControl("txt_Qt"), TextBox).Text & _
                                                                            " WHERE FK_ID_intEmpresa=" & SQLUtils.MyEmp & _
                                                                                  " AND FK_ID_intFuncionario=" & txt_psa_num_empregado.Text & _
                                                                                  " AND datDataDespesa='" & Format(dIni, "yyyyMMdd") & _
                                                                                  "' AND FK_ID_intTipoDespesa=(SELECT ID_intTipoDespesa " & _
                                                                                                              "FROM tblTiposDespesa " & _
                                                                                                              "WHERE FK_ID_intEmpresa=" & SQLUtils.MyEmp & _
                                                                                                                    " AND strTipoDespesa='" & CType(e.Item.FindControl("ddl_Tipo"), DropDownList).SelectedItem.Text & "')"
              sqlComm2.Parameters.Add("@sSQL", SqlDbType.NVarChar).Value = "SELECT NULL " & _
                                                                            "FROM tblDespesas " & _
                                                                            "WHERE FK_ID_intEmpresa=" & SQLUtils.MyEmp & _
                                                                                  " AND FK_ID_intFuncionario=" & txt_psa_num_empregado.Text & _
                                                                                  " AND datDataDespesa='" & Format(dIni, "yyyyMMdd") & _
                                                                                  "' AND FK_ID_intTipoDespesa=(SELECT ID_intTipoDespesa " & _
                                                                                                              "FROM tblTiposDespesa " & _
                                                                                                              "WHERE FK_ID_intEmpresa=" & SQLUtils.MyEmp & _
                                                                                                                    " AND strTipoDespesa='" & CType(e.Item.FindControl("ddl_Tipo"), DropDownList).SelectedItem.Text & "')"
              sqlComm2.Parameters.Add("@dSQL", SqlDbType.NVarChar).Value = "DELETE tblDespesas " & _
                                                                            "WHERE FK_ID_intEmpresa=" & SQLUtils.MyEmp & _
                                                                                  " AND FK_ID_intFuncionario=" & txt_psa_num_empregado.Text & _
                                                                                  " AND datDataDespesa='" & Format(dIni, "yyyyMMdd") & _
                                                                                  "' AND FK_ID_intTipoDespesa=(SELECT ID_intTipoDespesa " & _
                                                                                                              "FROM tblTiposDespesa " & _
                                                                                                              "WHERE FK_ID_intEmpresa=" & SQLUtils.MyEmp & _
                                                                                                                    " AND strTipoDespesa='" & CType(e.Item.FindControl("ddl_Tipo"), DropDownList).SelectedItem.Text & "')"
              sqlComm2.Parameters.Add("@sDesc", SqlDbType.NVarChar).Value = "O funcionário " & txt_psa_nome_completo.Text & " da empresa " & SQLUtils.MyEmp & _
                                                                              " inseriu uma despesa do tipo " & CType(e.Item.FindControl("ddl_Tipo"), DropDownList).SelectedItem.Text & _
                                                                              " com o valor de " & CType(e.Item.FindControl("txt_Qt"), TextBox).Text & "€ para o dia " & _
                                                                              Format(dIni, "dd-MM-yyyy")
              sqlComm2.Parameters.Add("@sUser", SqlDbType.NVarChar, 256).Value = User.Identity.Name
              sqlComm2.ExecuteNonQuery()

Open in new window

I then have this stored procedure to run the SQL:
ALTER PROCEDURE [dbo].[usp_ExecSQL]
	@Emp int,
	@Num int,
	@dIni varchar(14),
	@iSQL nvarchar(MAX),
	@uSQL nvarchar(MAX),
	@sSQL nvarchar(MAX),
	@dSQL nvarchar(MAX),
	@sDesc nvarchar(MAX),
	@sMot varchar(MAX),
	@sUser nvarchar(256),
	@cOper char(1)

AS

DECLARE @SQL nvarchar(MAX),@bSel bit
SET @SQL=N'SET @bSel=CASE WHEN EXISTS ('+@sSQL+N') THEN 1 ELSE 0 END'

IF @cOper='D'
BEGIN
	EXEC sp_executeSQL @dSQL
END
ELSE
BEGIN
	EXEC sp_executesql @SQL,N'@bSel bit OUTPUT',@bSel=@bSel OUTPUT
	IF @bSel='True'
		EXEC sp_executeSQL @uSQL
	ELSE
		EXEC sp_executeSQL @iSQL
END

DELETE tblAlterEmpregado
WHERE FK_ID_intEmpresa=@Emp
	AND FK_ID_intFuncionario=@Num
	AND strSelectSQL=@sSQL
	AND strInsertSQL=@iSQL
	AND strUpdateSQL=@uSQL
	AND strDeleteSQL=@dSQL
	AND strDesc=@sDesc
	AND strAccao=@cOper

Open in new window

I know I can change the stored procedure to read from the SQL directly from the table. If there's any injection, the table won't match and it won't run.

However, I'm having some difficulty getting some way to prevent injection on the code behind. Even though I'm using parameters, the query itself is being built by concatenation. If someone were to inject code on one of the controls, they could adulterate the query completely.

Is there any way to prevent this? I'm not sure this is even the best way to do this, but there has to be a safer way for it.
0
Comment
Question by:Cluskitt
  • 6
  • 2
9 Comments
 
LVL 14

Expert Comment

by:Juan Ocasio
ID: 39167578
How would you know the difference between a valid stored SQL statement and an injected SQL Statement?
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 39167590
When I load the repeater, I place the queries on labels. If I then send the queries to the procedure (as parameters), instead of running them blindly, I can do something like:
SELECT strSelectSQL FROM tblAlterEmpregado WHERE FK_ID_intEmpresa=@Emp AND FK_ID_intFuncionario=@Num AND strSelectSQL=@sSQL

Open in new window

(actual query would have more conditions, but this is just an example).
If the query has been injected, it won't match the one on the table and it won't run.

However, that won't solve injection problems when I place the query there in the first place.
0
 
LVL 14

Expert Comment

by:Juan Ocasio
ID: 39167619
Why can't you check the query against the stored query at the beginning of the SP against the one in the table.  If it doesn't match, discard it at the onset.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 39167624
That is what I said I could do. However, when I want to insert the query in the table in the first place, I'm open to injection, even though I'm using a parameter, as you can see in my code behind.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39167714
>> If it doesn't match, discard it at the onset.
?? how would you validly update any part

Not sure I can add much here, it's quite ambitious. I've used user accessible (some users!) tables to store 'queries', but never DDL/DML which is a more complex matter altogether.

for the stored queries we disallowed any stored information to commence with 'select' so that the executed code would be concatenated by that keyword, then if it fails it fails, but at least any danger is restricted to a selection. I have also used (in Oracle) the path of running the stored queries as 'create or replace view ....' as a validation (there were other benefits from this also)..

boa sorte com esse projeto
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 39167783
I don't really have a problem with executing the queries. My problem lies more with the initial protection of the query. After that, I can protect from injection by validating input against the actual table. If any of the 4 queries are tampered with between IIS and MS SQL, the query won't match and nothing will run.

Usually, using SqlCommand (or SqlDataAdapter) with parameters is enough to prevent injection. However, the query itself here is inside a parameter. It's built by concatenation. I don't know how I can prevent injection in this case. After all, I'm storing a query inside another query. Parameters don't really apply there.

However, a way must be found. If there's a better way to achieve this, I'm open to suggestions.

Obrigado pelo apoio.
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 0 total points
ID: 39174053
I have found a solution. It's kinda simple, really. I already had something similar being used in another unrelated code. Instead of concatenating the query into the parameters, what I have to do is create the query inside the CommandText concatenating WITH the parameters. That is, I needed to change what I had above to this:
              Dim sqlComm2 As New SqlCommand("INSERT INTO tblAlterEmpregado " & _
                                             "SELECT (SELECT UserID FROM aspnet_users WHERE UserName=@sUser)," & _
                                                    "@sSuper,@Emp,@Num,GETDATE()," & _
                                                    "'INSERT INTO tblDespesas " & _
                                                    "SELECT '+CAST(@Emp AS varchar(6))+','+CAST(@Num AS varchar(6))+','+@dIni+',ID_intTipoDespesa,'+@rQt+'" & _
                                                    " FROM tblTiposDespesa " & _
                                                    "WHERE FK_ID_intEmpresa='+CAST(@Emp AS varchar(6))+'" & _
                                                          " AND strTipoDespesa='+@sTipo," & _
                                                    "UPDATE tblDespesas " & _
                                                    "SET dblValorDespesa='+@rQt+'" & _
                                                    " WHERE FK_ID_intEmpresa='+CAST(@Emp AS varchar(6))+'" & _
                                                          " AND FK_ID_intFuncionario='+CAST(@Num AS varchar(6))+'" & _
                                                          " AND datDataDespesa='+@dIni+'" & _
                                                          " AND FK_ID_intTipoDespesa=(SELECT ID_intTipoDespesa " & _
                                                                                      "FROM tblTiposDespesa " & _
                                                                                      "WHERE FK_ID_intEmpresa='+CAST(@Emp AS varchar(6))+'" & _
                                                                                            " AND strTipoDespesa=+'@sTipo+')'," & _
                                                    "SELECT NULL " & _
                                                    "FROM tblDespesas " & _
                                                    " WHERE FK_ID_intEmpresa='+CAST(@Emp AS varchar(6))+'" & _
                                                          " AND FK_ID_intFuncionario='+CAST(@Num AS varchar(6))+'" & _
                                                          " AND datDataDespesa='+@dIni+'" & _
                                                          " AND FK_ID_intTipoDespesa=(SELECT ID_intTipoDespesa " & _
                                                                                      "FROM tblTiposDespesa " & _
                                                                                      "WHERE FK_ID_intEmpresa='+CAST(@Emp AS varchar(6))+'" & _
                                                                                            " AND strTipoDespesa=+'@sTipo+')'," & _
                                                    "DELETE tblDespesas " & _
                                                    " WHERE FK_ID_intEmpresa='+CAST(@Emp AS varchar(6))+'" & _
                                                          " AND FK_ID_intFuncionario='+CAST(@Num AS varchar(6))+'" & _
                                                          " AND datDataDespesa='+@dIni+'" & _
                                                          " AND FK_ID_intTipoDespesa=(SELECT ID_intTipoDespesa " & _
                                                                                      "FROM tblTiposDespesa " & _
                                                                                      "WHERE FK_ID_intEmpresa='+CAST(@Emp AS varchar(6))+'" & _
                                                                                            " AND strTipoDespesa=+'@sTipo+')'," & _
                                                    "@sDesc,'C','D'", _
                                             sConn)
              sqlComm2.Parameters.Add("@Emp", SqlDbType.Int).Value = SQLUtils.MyEmp
              sqlComm2.Parameters.Add("@Num", SqlDbType.Int).Value = txt_psa_num_empregado.Text
              sqlComm2.Parameters.Add("@dIni", SqlDbType.VarChar, 8).Value = Format(dIni, "yyyyMMdd")
              sqlComm2.Parameters.Add("@rQt", SqlDbType.VarChar, 12).Value = CType(e.Item.FindControl("txt_Qt"), TextBox).Text
              sqlComm2.Parameters.Add("@sTipo", SqlDbType.VarChar, 100).Value = CType(e.Item.FindControl("ddl_Tipo"), DropDownList).SelectedItem.Text
              sqlComm2.Parameters.Add("@sDesc", SqlDbType.NVarChar).Value = sBody
              sqlComm2.Parameters.Add("@sUser", SqlDbType.NVarChar, 256).Value = User.Identity.Name
              sqlComm2.Parameters.Add("@sSuper", SqlDbType.UniqueIdentifier).Value = Table1.Rows(0).Item(0)
              sqlComm2.ExecuteNonQuery()

Open in new window

Now, as in a normal query, if they try SQL injection, it will be inserted into the table as a normal varchar, rendering it harmless.
0
 
LVL 18

Author Comment

by:Cluskitt
ID: 39174074
BTW, I changed the stored procedure, as I mentioned above, to:
ALTER PROCEDURE [dbo].[usp_ExecSQL]
	@Emp int,
	@Num int,
	@dIni varchar(14),
	@iSQL nvarchar(MAX),
	@uSQL nvarchar(MAX),
	@sSQL nvarchar(MAX),
	@dSQL nvarchar(MAX),
	@sDesc nvarchar(MAX),
	@sMot varchar(MAX),
	@sUser nvarchar(256),
	@sSuper nvarchar(256),
	@cOper char(1)

AS

DECLARE @SQL nvarchar(MAX),@bSel bit,@iSQL2 nvarchar(MAX),@uSQL2 nvarchar(MAX),@sSQL2 nvarchar(MAX),@dSQL2 nvarchar(MAX)
SET @iSQL2=(SELECT strInsertSQL
			FROM tblAlterEmpregado
			WHERE FK_ID_intEmpresa=@Emp
				AND FK_ID_intFuncionario=@Num
				AND strInsertSQL=@iSQL
				AND strUpdateSQL=@uSQL
				AND strSelectSQL=@sSQL
				AND strDeleteSQL=@dSQL
				AND FK_UserID=(SELECT UserID FROM aspnet_users WHERE UserName=@sUser)
				AND FK_UserID_Super=(SELECT UserID FROM aspnet_users WHERE UserName=@sSuper)
				AND strAccao=@cOper)
SET @uSQL2=(SELECT strUpdateSQL
			FROM tblAlterEmpregado
			WHERE FK_ID_intEmpresa=@Emp
				AND FK_ID_intFuncionario=@Num
				AND strInsertSQL=@iSQL
				AND strUpdateSQL=@uSQL
				AND strSelectSQL=@sSQL
				AND strDeleteSQL=@dSQL
				AND FK_UserID=(SELECT UserID FROM aspnet_users WHERE UserName=@sUser)
				AND FK_UserID_Super=(SELECT UserID FROM aspnet_users WHERE UserName=@sSuper)
				AND strAccao=@cOper)
SET @sSQL2=(SELECT strSelectSQL
			FROM tblAlterEmpregado
			WHERE FK_ID_intEmpresa=@Emp
				AND FK_ID_intFuncionario=@Num
				AND strInsertSQL=@iSQL
				AND strUpdateSQL=@uSQL
				AND strSelectSQL=@sSQL
				AND strDeleteSQL=@dSQL
				AND FK_UserID=(SELECT UserID FROM aspnet_users WHERE UserName=@sUser)
				AND FK_UserID_Super=(SELECT UserID FROM aspnet_users WHERE UserName=@sSuper)
				AND strAccao=@cOper)
SET @dSQL2=(SELECT strDeleteSQL
			FROM tblAlterEmpregado
			WHERE FK_ID_intEmpresa=@Emp
				AND FK_ID_intFuncionario=@Num
				AND strInsertSQL=@iSQL
				AND strUpdateSQL=@uSQL
				AND strSelectSQL=@sSQL
				AND strDeleteSQL=@dSQL
				AND FK_UserID=(SELECT UserID FROM aspnet_users WHERE UserName=@sUser)
				AND FK_UserID_Super=(SELECT UserID FROM aspnet_users WHERE UserName=@sSuper)
				AND strAccao=@cOper)
SET @SQL=N'SET @bSel=CASE WHEN EXISTS ('+@sSQL2+N') THEN 1 ELSE 0 END'

IF @cOper='D'
BEGIN
	EXEC sp_executeSQL @dSQL2
END
ELSE
BEGIN
	EXEC sp_executesql @SQL,N'@bSel bit OUTPUT',@bSel=@bSel OUTPUT
	IF @bSel='True'
		EXEC sp_executeSQL @uSQL2
	ELSE
		EXEC sp_executeSQL @iSQL2
END

DELETE tblAlterEmpregado
WHERE FK_ID_intEmpresa=@Emp
	AND FK_ID_intFuncionario=@Num
	AND strSelectSQL=@sSQL
	AND strInsertSQL=@iSQL
	AND strUpdateSQL=@uSQL
	AND strDeleteSQL=@dSQL
	AND strDesc=@sDesc
	AND strAccao=@cOper

Open in new window

Now, if there's any tampering with any of the queries, there won't be a match and nothing will be executed.
0
 
LVL 18

Author Closing Comment

by:Cluskitt
ID: 39183828
Found the right solution. Thanks for your support.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now