Solved

Storing dynamic SQL on a table field and preventing SQL injection

Posted on 2013-05-15
9
356 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can't connect to new installation of SQL Server 2016 6 27
datetime in sql 6 23
email about the whoisactive result 7 21
SQL Query Syntax Join 4 27
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

919 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

12 Experts available now in Live!

Get 1:1 Help Now