VB Variables and SQL Queries

  • Thread starter Thread starter John Wildes
  • Start date Start date
J

John Wildes

Hello

How do I pass a variable containing data to an SQL Query. Here is my query
SELECT trn_date AS `Transaction Date`, item AS `Item Number`, type AS
`Policy Type`, pol AS `Policy Number`, `desc` AS Description, amt AS Amount,
rec AS `Customer #`, name AS Name, attn AS Attention,
street AS Street, city AS City, st AS State, zipcode AS ZIP
FROM invoice
ORDER BY rec

I'd like to insert a WHERE clause, WHERE INVOICE.REC="custID" with custID
being a string variable that the user enters into a text field. I know a
little bit about parameter queries and SQL, and again I am a noob when it
comes to VB. If someone can point me in the right direction that would be
great.

Thanks for all the help you guys have been this, is a great ng. Hopefully I
can contribute some in the days ahead.

john
 
John Wildes said:
I'd like to insert a WHERE clause, WHERE INVOICE.REC="custID" with custID
being a string variable that the user enters into a text field. I know a
little bit about parameter queries and SQL, and again I am a noob when it
comes to VB. If someone can point me in the right direction that would be
great.
Click to expand...

John... if you haven't already developed bad habits... you may as well not
start now :-) Generally speaking the concatenation method of building a SQL
query is frowned upon because (used in the wrong places... and it isn't
always obvious where those places are) it opens your database to malicious
mischief through a process known as SQL Injection.

Unless you are very careful to check the input somebody can enter additional
SQL code which will pass along to the server with yours...

May as well use parameters (or stored procedures) from the start,
Tom
 
Hi Tom,

Forgive me, but that is about as paranoid as I've seen. There must be
hundreds of hackers out there just waiting for John to make a mistake!

Get serious - passing a variable to the sql string as I demonstrated is not
only common but practical.

Bernie
 
I have to agree w Bernie

and usually you are putting id's from comboboxes in there, no real danger.
if you create a like(% string %) structure you have to look out but if you
test your input on symbols like ', / and remove them or make the user give
correct input, the danger is a lot smaller 2.

Don't get me wrong I normally use sp's 2, but not for every select statement

eric
 
Bernie Yaeger said:
Hi Tom,

Forgive me, but that is about as paranoid as I've seen. There must
be hundreds of hackers out there just waiting for John to make a
mistake!
Click to expand...

I agree with Tom. If you can exclude any risk why not go this way right from
the start?
Get serious - passing a variable to the sql string as I demonstrated
is not only common but practical.
Click to expand...

I think, working with Parameters instead is more common and practical. ;-)

Just my two Euro-cents. :)
 
Bernie Yaeger said:
Hi Tom,

Forgive me, but that is about as paranoid as I've seen. There must be
hundreds of hackers out there just waiting for John to make a mistake!

Get serious - passing a variable to the sql string as I demonstrated is not
only common but practical.
Click to expand...

Bernie it isn't a paranoia issue. I am not suggesting he carries a gun when
he walks around during the day... I' m suggesting he avoid walking in the
bad section of town at 2 a.m.

I'm sure you are right and nobody wants access to John's test data but that
won't be the last piece of code he writes. And code he writes in the future
will be influenced by what he learns today. He isn't going to "all of
sudden" know about security when he writes his first commercial app and the
risks aren't only from the outside. Employees can use the same methods to
discover things or just to damage systems if they get mad.

I brought the issue to his attention.
Tom
 
Wow, Thanks for the responses. I am going to side with Tom and Armin this
time, and I would like to learn how to work with Parameters and Stored
Procedures. For this application I am working on, I am querying a set of
DB3 tables. Only to pull information out, and drop it into a nicely
formated Crystal Report, not looking to update or delete any records. The
user has to be able to look up the records using a customer code, this
customer code is alpha numeric 7 digits / characters long, ex. WILDEJO1
would be my code. I want the user to be able to enter the customer code in
the text box, click search and have it return the recordset. How would I
accomplish this using the parameter or stored procedure way?

Thanks for the help again,
john
 
you are right of cource
but most of the time no direct user input is insert in the sql command (only
internal id values) not mutch risk there
and if you take direct user input like John wants to do you can check the
input first and only if it is to your liking add it to the sql command. (to
your liking could be numeric, only letters and certainly not containing ' )

why am i saying this
well there are times that sp's won't do the trick :/
imagine a structure where you can't know in advance how many levels you are
going to need, ex groups have articles and groups, those groups have their
own groups ...., if you have to get the whole structure you have to build
your sql dynamicly.
if someone knows how to do this in a sp let me know ;)

eric
 
Hi Tom,

I think this danger exist only with commercial windowforms applications that
reach a commercial centralized "open" database direct over the internet
(without any password protection, and not with a webservice).

For the rest the SQLstring is not going out of the door.

That first sitiation can be decompilated, (what obfuscator you make it will
be always possible).

But in that first is also the connection string which opens the database
more than the SQL string.

A simple test with a repeat Select * from "logical test from posibilities"
open with the connection string complete the database.

I think that when you do that strange thing. (Distrubiting Windowforms with
connection Strings in it that connect to your own open SQL server). You
should be warned to take another approach.

For this I don't see it, but if I am wrong please correct me?

My half eurocent

Cor
 
hmm crystal
the easyest but worst performance way is to give all the data to your
crystal report and use a filter on a parameter in the report to show the
right fields
if you are talking about small amounts of data this could do
have a look here
http://www.crystalreportsbook.com/Chapter05.asp

the best way would be to filter the data in advance for that im listening 2
;)

eric
 
Tom Leylan said:
John... if you haven't already developed bad habits... you may as well not
start now :-) Generally speaking the concatenation method of building a SQL
query is frowned upon because (used in the wrong places... and it isn't
always obvious where those places are) it opens your database to malicious
mischief through a process known as SQL Injection.

Unless you are very careful to check the input somebody can enter additional
SQL code which will pass along to the server with yours...

May as well use parameters (or stored procedures) from the start,
Tom
Click to expand...

I agree with Tom, because it looks like your doing some sort of
Invoicing, always use stored procedures.
Take the time to research stored procedures in which ever rdbms you
are using. (MS SQLServer, Oracle)
 
John Wildes said:
Wow, Thanks for the responses. I am going to side with Tom and Armin this
time, and I would like to learn how to work with Parameters and Stored
Procedures.
Click to expand...

Hi John: Glad to hear it :-) But honestly it isn't about taking sides...
there have been malicious users/hackers/crackers around for decades see: The
Cuckoo's Egg by Clifford Stoll. Would you want to spent two years and
$120,000 writing code to protect your home inventory database? Of course
not but if it takes 5 to 10 minutes and costs nothing extra, why not?

I'll suggest you first, check the MSDN documentation... but then search
Google for an article or two on the subject of ADO.Net queries and stored
procedures If you have some spare time read an article or two on some of
the risks by searching for "SQL injection".

Tom
 
Hi Eric,
but most of the time no direct user input is insert in the sql command (only
internal id values) not mutch risk there
Click to expand...

I have no idea about what happens most of the time. If the user enters an
account and password or is searching for lastname or customer Id and things
like that they are often keyed in.
why am i saying this
well there are times that sp's won't do the trick :/
Click to expand...

If a stored procedure won't do the trick then you can't use a stored
procedure. But you shouldn't open yourself to a problem unnecessarily and
when that situation presents itself you deal with it as best you can.
 
Hi Cor:
I think this danger exist only with commercial windowforms applications that
reach a commercial centralized "open" database direct over the internet
(without any password protection, and not with a webservice).
Click to expand...

I don't think so :-) The docs and faqs about such attacks outline hacking
from the account/password prompt.
For the rest the SQLstring is not going out of the door.
Click to expand...

Most crime is an "inside" job.

BTW it isn't about decompilation or a connection string... this didn't start
with .Net. Somebody could just be mad at you and decide to crash your
system. Or if they got fired maybe they just want to ruin something as they
leave.

Here's a pdf document outlining it
http://www.nextgenss.com/papers/advanced_sql_injection.pdf

and here is a short article
http://www.sitepoint.com/article/794
 
Hi John,

If you believe that the sp way is the best way to go, I can help you, even
though - for many reasons not worth rehashing - I don't agree with our merry
band, or at least not for the reasons they articulate.

But specifically with regard to crystal, you can pass the textbox.text as a
parameter to crystal - it's a bit arcane, but I can show you how to do this.
This parameter can then be used in a record selection formula inside
crystal.

Re a 'dynamic' sp, I can show you how to do that as well - you will need a
'drop sp' sp and then you will be able to dynamically rebuild the sp for
each circumstance. This is actually pretty easy to do using the command
object and executenoquery.

Let me know if I can be of help.

Bernie
 
Hi John,

And - btw - if it's a 'one customer code each time', what you should use is
a combobox or a listview, not a textbox, making lookup and report production
one step and avoiding any keying errors.

Bernie
 
John (through Bernie's message) :-)

I've got an idea. Why don't you post the gist of the code you have and we
can try out various ways to improve it? That way everybody can take their
pick based upon what they think is a safe, easy, fast, reliable, etc.,
solution.

Tom
 
Couldn't resist this as have been doing this sort of thing since VB4
and picked up a few tidbits along the way.

There are several reasons to use SP's here, only one of which is
security. Database integrity is a major reason, sp's will prevent the
user from entering invalid data into the database. They also improve
performance considerably. But the reason I like them the most is that
they generally reduce the amount of code I write (and consequently,
the number of mistakes I make :).
 
John,
You sure stirred up a hornets nest with this one!

When the number of possible permutations of criteria gets "excessive" (you
get to define this!) then the idea of a generic WhereClause parameter is a
good one. (you could also call it Filter).

======================================================
Since no one actually answered the original question - here is how you do
it:
======================================================
Dim mySQL as String
mySQL = CostCenterClass.GetCount("costcode=456")
======================================================
In a CostCenterClass you would have a shared method:

Public Shared Function GetCount(ByVal whereClause As String) As String
strSQL = "SELECT Count(*) FROM costcenter "
If whereClause <> String.Empty Then
strSQL &= "WHERE " & whereClause
End If
Return strSQL
End Function
======================================================

BTW - in the same class you could have some SQL statements use parameters:

Public Shared Function Insert() As String
If mDBType = "SQL Server" Then
strSQL = "INSERT INTO costcenter(costcode,ccname,status)
VALUES(@costcode,@ccname,@status)"
ElseIf mDBType = "Oracle" Then
strSQL = "INSERT INTO costcenter(costcode,ccname,status)
VALUES(:costcode,:ccname,:status)"
End If
Return strSQL
End Function
 
Back
Top