Hi Stephen
Message: 7
Date: Thu, 24 Aug 2006 21:06:24 -0500
From: "Stephen the Cook" <[EMAIL PROTECTED]>
Subject: RE: Long SQL WHERE clauses
To: [EMAIL PROTECTED]
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain;charset="windows-1250"
Mike yearwood <> wrote:
> The ultimate answer IMO is to create your SQL as a parameterized
> query. No stupid string scrubbing required, no possibility of SQL
> injection attacks and the performance is the same as with a stored
> procedure since SQL version 7 and up cache all execution plans for
> all queries including stored procedures.
>
> The "raw user input" no matter what it contains is sent to SQL Server
> as a parameter, therefore it is never executed, while the rest of the
> SQL command is.
>
> lcName = "raw user input"
> sqlexec(gnconnection,"select * from table where name = ?m.lcName")
How do you apply a "Like" comparrison?
I'm building the Parameterized Ad-Hoc SQL with a UI where the user can
choose operators etc. If the want to do a like comparison they will
enter
lcName = "raw %"
and my command will look like
sqlexe(gnconnection,"select * from table where name like ?lcName")
Someone referenced this article:
http://www.sommarskog.se/dynamic_sql.html
For those of you who are not clear. Dynamic SQL is as fast as stored
procedures. If someone can enter ' in a text box that does not mean
you have a security problem, IF you use parameterized queries. That's
exactly what this guy says over and over!
HERE: as long as you take the sub-fork 1-b-ii, it does not have to be bad.
HERE: In application code, sp_executesql should be your choice 95% of
the time for reasons that will prevail.
HERE: The second parameter @params is optional, but you will use it
90% of the time.
HERE: SQL injection is possible as soon there is dynamic SQL which is
handled carelessly,
And believe me, I'm not doing it carelessly, but very carefully by
passing user entered values as parameters - and nothing else!
The issue is not that a user is allowed to enter anything they want.
The issue is what programmers do with that text. Concatenating the
value directly into the sql command is stupid. Using parameters to
hold the values and concatenating the parameter names into the query
neatly gets around any sql injection problem.
HERE: SQL injection is a serious security issue, and you must take
precautions to protect your applications against it.
Thankfully, it is not difficult at all. I've seen mentioning of
various ways to validate input data, but all that is a joke. There are
three steadfast principles you need to follow:
Never run with more privileges than necessary. Users that log into an
application with their own login should normally only have EXEC
permissions on stored procedures. If you use dynamic SQL, it should be
confined to reading operations so that users only need SELECT
permissions. A web site that logs into a database should not have any
elevated privileges, preferably only EXEC and (maybe) SELECT
permissions. Never let the web site log in as sa!
For web applications: never expose error messages from SQL Server to
the end user.
Always used parameterised statements. That is, in a T-SQL procedure
use sp_executesql, not EXEC().
The first point is mainly a safeguard, so that if there is a injection
hole, the intruder will not be able to do that much harm. The second
point makes the task for the attacker more difficult as he cannot get
feedback from his attempts.
But it is the third point that is the actual protection
HERE: Protection against SQL injection is not the only advantage of
using parameterised queries.
HERE: Starting with SQL 7, SQL Server also caches the plans for bare
statements sent from a client or generated through dynamic SQL.
HERE: with EXEC() you lose an important benefit of stored procedures
whereas with sp_executesql you don't.
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
