Charlotte Foust
cfoust at infostatsystems.com
Fri Oct 14 14:55:42 CDT 2005
Swell, define dynamic SQL. When it is compiled into a dll, is it still
dynamic? When your permissions to the back end are highly restricted
and all the SQL is created in the dll, is it still dynamic? I
understand the capabilites of sprocs. However, in an N-tier
architecture, you can build some of that same capability into the middle
tier and validate the data before it ever gets passed to the backend for
handling. So what special purpose is then served by the sproc?
Charlotte Foust
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Friday, October 14, 2005 12:04 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Data interface The best way
Not to say I told you so, but I have been harping on this for quite a
while. SQL injection is very real and very dangerous and to the extent
that you rely on dynamic SQL you put your arms down and dare the
opponent to punch you.
Sprocs and UDFs are way better protection because their params are
handled differently. That is not to argue that there is something
inherently wrong with coding an ADO approach into the DB, but it is to
say that where possible, use a sproc or a UDF rather than dynamic SQL.
There are (fortunately, relatively few) occasions when dynamic (i.e.
constructed in the front end) SQL is required, but in 9 out 10 cases
this turns out to be unnecessary. I will supply a very simple example,
trusting that you all can extrapolate. You have a form which can be
opened in two ways: a) supply a CustomerID and
b) supply no CustomerID. In either case, the form should display a
series of Sales (for one Customer or all Customers). The sproc to handle
this resembles this improvised code: SELECT *
FROM Sales
WHERE
CustomerID = @CustomerID
OR @CustomerID IS NULL
So there are only two possible outcomes.
1. The user supplies a valid or invalid CustomerID, or none at all. A
valid CustomerID may show zero+ orders. An invalid CustomerID shows
nothing.
2. No CustomerID shows all Sales.
Given such a sproc (improvised on the fly), SQL injection techniques
will mostly if not all be blocked. I could go into detail about why this
is so, but I have already done so. The basic point is, if a sproc is
expecting say an integer and instead receives a string (a SQL injection
attempt) then no problem. The attempt will get busted at the border of
the sproc. When several params are passed, the logic escalates
arithmetically but not geometrically. I.e. if you are expecting 5
params, it`s not that big a problem... and who in the sane world writes
a sproc expecting 50 params... LOL. One of the greatest features of
sprocs is their ability to test incoming data. If it fails to meet
expectations (God, I am sounding like my ex-wife LOL), then the data is
cast aside and the sproc terminates. OTOH, if you build dynamic SQL
statements then fling them at the engine, who knows how much s**t will
stick to the wall? As I said above, it is not always possible to avoid
dynamic SQL. But to the extent that you CAN avoid it, you SHOULD avoid
it. Unless of course you are working for a company doomed to failure
anyway... in which case your contribution to the failure is unlikely to
be noticed. A. -----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: October 13, 2005 4:15 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Data interface The best way
At a recent MS conference the speaker discussed the whole issues around
MS SQL security.
It was stated that if a raw sql request can be made from the client end,
regardless of its interface then your whole system is venerable. They
then proceeded to show a number of ways to hack-in to a MS SQL database
using various methods and security holes.
The lectures were very impressive and alarming.
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
Foust
Sent: Thursday, October 13, 2005 12:46 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Data interface The best way
No, the SQL is stored in the application, not the BE, but yes if we were
going exclusively against SQL Server, the sproc names would work
instead. Since our application is backend neutral at that level, we
don't do it that way. I've never heard that business about SQL in the
front end being a security risk before, so I can't comment on that.
These are windows apps, so that might be the difference. Given that our
apps, front and backend, are run on our clients's systems, I don't know
what kind of security risk would be posed by the SQL being in the front
end anyhow.
Charlotte Foust
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Thursday, October 13, 2005 11:31 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Data interface The best way
Charlotte, does that mean you are using dynamic SQL in the BE? I tend to
see a lot more of that now that I am starting to use .Net more and more.
Couldn't the xml file hold a list of sproc names and you could just
execute a sproc instead? I was always instructed not to create SQL
Strings in the front end as passing them to the back end was a security
risk. This is of course, unless you are dealing with the possibility of
SQL injection on the back end and not allowing certain characters and/or
reserved words.
Jim, are you asking how to make a connection into the SQL BE via code? I
had a bit of a time understanding how to do this (coming form an Access
background). I created a test page with has a cascading combo box for
automobile makes and models. Here is how I assign the rowsource to the
"Model" combo box after selecting a "Make" (or manufacturer):
Private Sub cboMake_SelectedIndexChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
cboMake.SelectedIndexChanged
If Me.SqlConnection1.State <> ConnectionState.Open Then
Me.SqlConnection1.Open()
Dim A As System.Data.SqlClient.SqlDataReader
Dim B As SqlClient.SqlCommand
B = New SqlClient.SqlCommand
B.Connection = Me.SqlConnection1
B.CommandText = "stpSelectModel " &
Me.cboMake.SelectedItem.Value
A = B.ExecuteReader()
Me.cboModel.DataSource = A
Me.cboModel.DataTextField = "Model"
Me.cboModel.DataValueField = "ModelID"
Me.cboModel.DataBind()
Me.SqlConnection1.Close()
End Sub
HTH
David
-----Original Message-----
From: Charlotte Foust
We use typed datasets and build a data "entity" in the solution that
inherits that typed dataset and handles validations, update calls,
fills, etc. We build dataproviders for each typed
dataset/entity/interface and let it handle creating the connections and
command strings, building the data adapter and passing a filled "entity"
back to the calling routine. We actually store the SQL for these
commands in xml and look up the one we need, passing the parameters in
through the dataprovider. It works quite well.
<snip>
Charlotte Foust
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Thursday, October 13, 2005 10:09 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Data interface The best way
Hi All:
I am current working building a .Net solution for a client. ASP.Net 2,
ADO.Net and MS SQL 200x.
Traditionally, when designing the system I have passed all the Selects,
Inserts, Updates and Deletes requests through to the server via ADO
commands using parameter lists and then to MS SQL side Stored Procedures
to manage any final validation and data work.
I am not sure of the best way to manage the data flow with this new
architecture (i.e. Datasets etc.). Any suggestions and experiences would
be very helpful.
TIA
Jim
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com