Posted by Erland Sommarskog on 06/08/05 18:59

vbnetrookie (bigjmt@hotmail.com) writes:
> I'm not sure what you mean :
> enter the following string for title ???
> WHat will this do and why add that line if it all works now???
> I'm alaways willing to learn new stuff so i'm all ears!!

Presumably title comes from an input field. And it is into this input
field you should enter this string and learn what happens. You may
think you are searching for some data, but in fact you will blow away
your table.

This is something which is called "SQL injection". By entering SQL commands
into an input field, an intruder might be able to do things your database
that you did not intend. This is particular a danger if that input field
in on a web site. The trick is simple: Use an ' to close the string and
also a -- at the end to kill the syntax that comes after the query.

The remedy for this problem is simple: rather than building the complete
SQL statement, you use a parameterised statement:

Dim sqlStr As String = "SELECT DISTINCT Last_Name FROM [" & PubName &
"] WHERE PostalCode = @postalcode And Title= @title "

You then add the parameters with .AddParameter which I believe is on
the command object. (I'm not a very frequent ADO .Net programmer, so
I don't remember the details.) I encourage you to look it up. And I
cannot stress enough that this is essential stuff.

As you may note, I did not use a parameter for the table name; This is
because table names cannot be parameterized. Usually if you find the need
to determine the table name dynamically, this is a strong indiciation of
a poor database design.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]