store T-SQL in database

[Stephen Witter]

I have a asp.net page that creates controls at runtime. I am also
wanting to create drop down lists at runtime that connect to a view in
sql server. I was thinking of storing either the view name of the sql
statement in a field, and using that to create the datasource for the
ddl at runtime. I was thinking I could store the connections strings
in variables since there would only be two or three. My main concern
is whether there is any security risks involved in storing sql
statments in a field, and accessing that using ado in my code. for
example:

function GetDataSource() as string
dim sql as string = "SELECT SQL_STATMENT FROM REPORT_TABLE WHERE
REPORT_ID=1"
GetDataSource=r st("SQL_STATMEN T").value
end function

this would get a sql statment from the field and use it as follows:

sub BindDDL()
Dim ConnectionStrin g As String = "server=;UID=;d atabase=;PWD=;"
Dim CommandText As String = GetDataSource()
Dim myConnection As New SqlConnection(C onnectionString )
Dim myCommand As New SqlCommand(Comm andText, myConnection)
ddl.DataTextFie ld = "field1"
ddl.DataValueFi eld = "field2"
myConnection.Op en()
ddl.DataSource =
myCommand.Execu teReader(Comman dBehavior.Close Connection)
ddl.DataBind()
end sub

Again, my only concern is security.

[William Ryan]

Re: store T-SQL in database

Yes, that's a security risk unless you store it encrypted. I highly
recommend against this approach Storing SQL Statements in a DB is fine, but
in general, the less that you expose (ie hide behind tightly permissioned
procs) the better.

Here's a great article on the subject...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT11.asp

"Stephen Witter" <switter@medami cus.com> wrote in message
news:183d9a35.0 312031830.66b65 13b@posting.goo gle.com...[color=blue]
> I have a asp.net page that creates controls at runtime. I am also
> wanting to create drop down lists at runtime that connect to a view in
> sql server. I was thinking of storing either the view name of the sql
> statement in a field, and using that to create the datasource for the
> ddl at runtime. I was thinking I could store the connections strings
> in variables since there would only be two or three. My main concern
> is whether there is any security risks involved in storing sql
> statments in a field, and accessing that using ado in my code. for
> example:
>
> function GetDataSource() as string
> dim sql as string = "SELECT SQL_STATMENT FROM REPORT_TABLE WHERE
> REPORT_ID=1"
> GetDataSource=r st("SQL_STATMEN T").value
> end function
>
> this would get a sql statment from the field and use it as follows:
>
> sub BindDDL()
> Dim ConnectionStrin g As String = "server=;UID=;d atabase=;PWD=;"
> Dim CommandText As String = GetDataSource()
> Dim myConnection As New SqlConnection(C onnectionString )
> Dim myCommand As New SqlCommand(Comm andText, myConnection)
> ddl.DataTextFie ld = "field1"
> ddl.DataValueFi eld = "field2"
> myConnection.Op en()
> ddl.DataSource =
> myCommand.Execu teReader(Comman dBehavior.Close Connection)
> ddl.DataBind()
> end sub
>
> Again, my only concern is security.[/color]