?
Solved

Storing dynamic SQL on a table field and preventing SQL injection

Posted on 2013-05-15
9
Medium Priority
?
363 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 49

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

719 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