Solved

Storing dynamic SQL on a table field and preventing SQL injection

Posted on 2013-05-15
9
357 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
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 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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

776 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