469,277 Members | 2,241 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,277 developers. It's quick & easy.

Query SQL using variables.

Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

The code works, and I get my results.

My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"

I lack the knowladge ƒ¼ could anyone help me out here?
Code sample would be GREATLY appreciated
Jan 1 '08 #1
17 1587

<R.*****@gmail.comwrote in message
news:3e**********************************@t1g2000p ra.googlegroups.com...
Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

The code works, and I get my results.

My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"

I lack the knowladge ƒ¼ could anyone help me out here?
Code sample would be GREATLY appreciated
Dim var1 as string
Dim var2 as string
dim sqlstr as string

var1 = "hello"
var2 = "to me"
sqlstr = "Select * from contacts WHERE CITY = " &var1 &" AND TYPE = " &var2

SqlDataAdapter(strsql, sconn)

if the data you needed was numeric, then you do this.

dim var1 as int
dim var2 as int

var1 = 9
var2 = 10
sqlstr = "Select * from contacts WHERE CITY CODE = " &var1.ToSrting() &" AND
TYPE = " &var2.ToString()

Jan 1 '08 #2
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.comwrote:
<R.Ra...@gmail.comwrote in message

news:3e**********************************@t1g2000p ra.googlegroups.com...
Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

The code works, and I get my results.

My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"

I lack the knowladge ƒ¼ could anyone help me out here?
Code sample would be GREATLY appreciated

Dim var1 as string
Dim var2 as string
dim sqlstr as string

var1 = "hello"
var2 = "to me"

sqlstr = "Select * from contacts WHERE CITY = " &var1 &" AND TYPE = " &var2

SqlDataAdapter(strsql, sconn)

if the data you needed was numeric, then you do this.

dim var1 as int
dim var2 as int

var1 = 9
var2 = 10

sqlstr = "Select * from contacts WHERE CITY CODE = " &var1.ToSrting() &" AND
TYPE = " &var2.ToString()
Hi Mr.arnold and thanks for the reply!
it gives me a syntex error on the sqlstr, it's just wont use the
vars...
and it's text i query not numeric.

any suggestions?
Jan 1 '08 #3

<R.*****@gmail.comwrote in message
news:71**********************************@q77g2000 hsh.googlegroups.com...
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.comwrote:
<R.Ra...@gmail.comwrote in message

news:3e**********************************@t1g2000p ra.googlegroups.com...
<snipped>
Hi Mr.arnold and thanks for the reply!
it gives me a syntex error on the sqlstr, it's just wont use the
vars...
and it's text i query not numeric.

You can use the ToString() for the string data too.

any suggestions?
----------------
What you need to do is put a brake point on the line right after the build
of the strsql variable.

You then need to use the Quick Watch, copy the text of the string data that
was created for the SQL statement. You can right-click the variable to get
to Quick Watch.

If you have SQL Server on the machine and it is SQL Server 2000, then you
will start SQL Server Query Analyzer and past the SQL text into the pane,
highlight and execute it.

Or if it is SQL 2005 that you have, then you use the SQL Server Management
Studio, New Query, and past the text in the pane and highlight and execute
it.

Let SQL Server tell you what's wrong with the statement by using one of the
solutions above..

Jan 1 '08 #4

<R.*****@gmail.comwrote in message
news:71**********************************@q77g2000 hsh.googlegroups.com...
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.comwrote:
<R.Ra...@gmail.comwrote in message
any suggestions?

Oh, you might have to do this that is put quotes around the variables, since
it's string data.

sqlstr = "Select * from contacts WHERE CITY = " &"'" &var1 &"'" &" AND TYPE
= " &"'" &var2 &"'"
Jan 1 '08 #5
On Jan 1, 4:23 pm, "Mr. Arnold" <MR. Arn...@Arnold.comwrote:
<R.Ra...@gmail.comwrote in message

news:71**********************************@q77g2000 hsh.googlegroups.com...
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.comwrote:
<R.Ra...@gmail.comwrote in message

any suggestions?

Oh, you might have to do this that is put quotes around the variables, since
it's string data.

sqlstr = "Select * from contacts WHERE CITY = " &"'" &var1 &"'" &" AND TYPE
= " &"'" &var2 &"'"
Hey Arnold,
this time the string you gave me worked!
only now i get a different error which is my fault since I forgot to
say..

the second var, returns a text and symbol, for example (only - and + )
X-
X+
So the error I get now is:
Unclosed quotation mark after the character string ''.
An expression of non-boolean type specified in a context where a
condition is expected, near ''.

I know the string work..because I edited the string and it worked with
no error:
sqlstr = "Select * from contacts where city = " & "'" & var1 & "'"

any suggestions?
Jan 1 '08 #6

<R.*****@gmail.comkirjoitti viestissä
news:24**********************************@w56g2000 hsf.googlegroups.com...
I know the string work..because I edited the string and it worked with
no error:
sqlstr = "Select * from contacts where city = " & "'" & var1 & "'"

any suggestions?
Try this:

sqlstr = "Select * from contacts WHERE CITY = '" & var1 _
& "' AND TYPE='" & var2 & "';"

I have to warn you that this kind of query is very dangerous because users
can include '-characters to the query and make SQL injections to your
database.

-Teemu
Jan 1 '08 #7

<R.*****@gmail.comwrote in message
news:24**********************************@w56g2000 hsf.googlegroups.com...
On Jan 1, 4:23 pm, "Mr. Arnold" <MR. Arn...@Arnold.comwrote:
><R.Ra...@gmail.comwrote in message

news:71**********************************@q77g200 0hsh.googlegroups.com...
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.comwrote:
<R.Ra...@gmail.comwrote in message

any suggestions?

Oh, you might have to do this that is put quotes around the variables,
since
it's string data.

sqlstr = "Select * from contacts WHERE CITY = " &"'" &var1 &"'" &" AND
TYPE
= " &"'" &var2 &"'"

Hey Arnold,
this time the string you gave me worked!
only now i get a different error which is my fault since I forgot to
say..

the second var, returns a text and symbol, for example (only - and + )
X-
X+
So the error I get now is:
Unclosed quotation mark after the character string ''.
An expression of non-boolean type specified in a context where a
condition is expected, near ''.

I know the string work..because I edited the string and it worked with
no error:
sqlstr = "Select * from contacts where city = " & "'" & var1 & "'"

any suggestions?
I think you have to get a closing quote in there at the end of the string.
This link may help you. You should next look into Stored Procedure and
passing parms/variables to the Stored Procedure and executing it, when you
become more comfortable in what your doing.

http://www.code-magazine.com/Article...uickid=0211121
Take the error message and past it into Google. I am sure you'll see a
solution to your problem.
>Unclosed quotation mark after the character string ''.
An expression of non-boolean type specified in a context where a
condition is expected, near ''.
Jan 1 '08 #8
Rafii,

First try to use the right names for the right things. By instance, don't
talke about a datagrid while you use a datagridview in your code. Those are
in fact two complete different kind of grids.

To review your code (including your question)
>>Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
>>Dim filterresults As New SqlDataAdapter("Select * from contacts ", sconn)
Try to keep the name, this is not a filterresult, this is a construction of
a DataAdapter.

This can be including your where clause

Dim da As New SqlDataAdapter("Select * from contacts WHERE CITY=@VARIABLE1
AND TYPE=@VARIABLE2", sconn)

I have let it, however it is never right to use a dataname as "Type", I
would in your place give that in your database and in your code another
name.
>>Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
The DataAdapter is never nothing, you have constructed it above therefore
this if is complete without any sence. Therefore you can remove it.

For selection in a select string it is for more than one reason good to use
parameters. It makes at least your code more visible and easy to handle.

da.SelectCommand.Parameters.Clear()
'The code above is in fact only needed all second times, however it does not
bother to place it in that instead an if or whatever more costly instruction
than the one above.

da.SelectCommand.Parameters.Add(New SqlParameter("@Variable1",
TheCityField))
da.SelectCommand.Parameters.Add(New SqlParameter("@Variable2",
TheTypeField))
>>filterresults.Fill(contactsresult)
this one I have changed in
da.Fill(contactresult) 'filterresult is in my idea a confusing answer it is
a result of the fill (at SQL side the name is ResultSet
>>filterresults.Dispose()
This dispose does nothing, the dispose is there because it is inherrited
from components, but that does not mean that you should use it.
>>End If
The End If above can go of course too away because of the removing of the if

Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

It would be better to set all this code inside a Try and Catch block,
however that would make it in my idea to confusing to tell it direct.

Cor

Jan 1 '08 #9
On Tue, 1 Jan 2008 04:23:40 -0800 (PST), R.*****@gmail.com wrote:
>Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

The code works, and I get my results.

My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"

I lack the knowladge ?? could anyone help me out here?
Code sample would be GREATLY appreciated
Use Parameters to supply the values. Cor has given you some code to
use.

Parameters do two thing for you over just putting the values you need
in the Select statement. First they format the value - no need to add
quotes around text, or put dates into whatever format the database
needs. Second they prevent SQL injection, which malicious users can
use to make arbitrary changes to your database.
Jan 1 '08 #10
On Jan 1, 6:42 am, R.Ra...@gmail.com wrote:
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.comwrote:


<R.Ra...@gmail.comwrote in message
news:3e**********************************@t1g2000p ra.googlegroups.com...
Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)
The code works, and I get my results.
My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"
I lack the knowladge ƒ¼ could anyone help me out here?
Code sample would be GREATLY appreciated
Dim var1 as string
Dim var2 as string
dim sqlstr as string
var1 = "hello"
var2 = "to me"
sqlstr = "Select * from contacts WHERE CITY = " &var1 &" AND TYPE = " &var2
SqlDataAdapter(strsql, sconn)
if the data you needed was numeric, then you do this.
dim var1 as int
dim var2 as int
var1 = 9
var2 = 10
sqlstr = "Select * from contacts WHERE CITY CODE = " &var1.ToSrting() &" AND
TYPE = " &var2.ToString()

Hi Mr.arnold and thanks for the reply!
it gives me a syntex error on the sqlstr, it's just wont use the
vars...
and it's text i query not numeric.

any suggestions?- Hide quoted text -

- Show quoted text -
I suggest you look at Cor's reply. While you can use concatenation,
as Mr. Arnold suggested - it is VERY bad practice. It opens you up to
quoting issues, and more importantly security issues.

--
Tom Shelton
Jan 1 '08 #11
While I, personally, would not use concatenation unless there was no
practical alternative and/or it was in a 'safe' environment, let's put
things in perspective and moderate your warning say that there could be a
security risk.

The significance of that risk depends on a number of factors that all have
to 'come together' simultaneously to allow a 'SQL injection attack'.

As for the other issues, (quoting, dates/times, formatting, etc.), they are
bread and butter issues and don't cause concatenation to be 'bad'.

Let's start off 2008 by encouraging people to use good practices rather than
kicking them in the teeth for using a practice that some MIGHT consider to
be 'bad'.
"Tom Shelton" <to*********@comcast.netwrote in message
news:c1**********************************@i12g2000 prf.googlegroups.com...
On Jan 1, 6:42 am, R.Ra...@gmail.com wrote:
On Jan 1, 2:49 pm, "Mr. Arnold" <MR. Arn...@Arnold.comwrote:


<R.Ra...@gmail.comwrote in message
news:3e**********************************@t1g2000p ra.googlegroups.com...
Hi,
I have a simple (?) question for you all experts.
I have a button that performs a query on my SQL and fill a datagrid on
the form
The code:
Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from
contacts ", sconn)
Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then
filterresults.Fill(contactsresult)
filterresults.Dispose()
End If
Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)
The code works, and I get my results.
My next step would be taking two variables from my code and use them
on the query IE: (which btw I collect from two combo boxes on the same
form)
Select * from contacts WHERE CITY=VARIABLE AND TYPE=VARIABLE"
I lack the knowladge ? could anyone help me out here?
Code sample would be GREATLY appreciated
Dim var1 as string
Dim var2 as string
dim sqlstr as string
var1 = "hello"
var2 = "to me"
sqlstr = "Select * from contacts WHERE CITY = " &var1 &" AND TYPE = "
&var2
SqlDataAdapter(strsql, sconn)
if the data you needed was numeric, then you do this.
dim var1 as int
dim var2 as int
var1 = 9
var2 = 10
sqlstr = "Select * from contacts WHERE CITY CODE = " &var1.ToSrting() &"
AND
TYPE = " &var2.ToString()

Hi Mr.arnold and thanks for the reply!
it gives me a syntex error on the sqlstr, it's just wont use the
vars...
and it's text i query not numeric.

any suggestions?- Hide quoted text -

- Show quoted text -
I suggest you look at Cor's reply. While you can use concatenation,
as Mr. Arnold suggested - it is VERY bad practice. It opens you up to
quoting issues, and more importantly security issues.

--
Tom Shelton

Jan 2 '08 #12
On 2008-01-02, Stephany Young <noone@localhostwrote:
While I, personally, would not use concatenation unless there was no
practical alternative and/or it was in a 'safe' environment, let's put
things in perspective and moderate your warning say that there could be a
security risk.

The significance of that risk depends on a number of factors that all have
to 'come together' simultaneously to allow a 'SQL injection attack'.

As for the other issues, (quoting, dates/times, formatting, etc.), they are
bread and butter issues and don't cause concatenation to be 'bad'.

Let's start off 2008 by encouraging people to use good practices rather than
kicking them in the teeth for using a practice that some MIGHT consider to
be 'bad'.
That's exactly what I was doing. I was warning him of the potential
risks of using concatenation to form his query strings. I don't believe
my warning was of a dire or overly sentationalist nature. It is a bad
practice - even in "safe" environments.

--
Tom Shelton
Jan 2 '08 #13

"Tom Shelton" <to*********@YOUKNOWTHEDRILLcomcast.netwrote in message
news:uV**************@TK2MSFTNGP02.phx.gbl...
On 2008-01-02, Stephany Young <noone@localhostwrote:
>While I, personally, would not use concatenation unless there was no
practical alternative and/or it was in a 'safe' environment, let's put
things in perspective and moderate your warning say that there could be a
security risk.

The significance of that risk depends on a number of factors that all
have
to 'come together' simultaneously to allow a 'SQL injection attack'.

As for the other issues, (quoting, dates/times, formatting, etc.), they
are
bread and butter issues and don't cause concatenation to be 'bad'.

Let's start off 2008 by encouraging people to use good practices rather
than
kicking them in the teeth for using a practice that some MIGHT consider
to
be 'bad'.

That's exactly what I was doing. I was warning him of the potential
risks of using concatenation to form his query strings. I don't believe
my warning was of a dire or overly sentationalist nature. It is a bad
practice - even in "safe" environments.
I don't consider it to be a bad practice. I have used them both over the
years Dynamic SQL statements and Stored Procedures. And for the OP to learn
the basics, I see nothing wrong with his approach. For simple pulling of
data from a database table, I don't see the harm.

Jan 2 '08 #14
On Jan 1, 5:00*pm, "Teemu" <tsir...@hotmail.comwrote:
<R.Ra...@gmail.comkirjoitti viestissänews:24********************************** @w56g2000hsf.googlegroups.com...
I know the string work..because I edited the string and it worked with
no error:
sqlstr = "Select * from contacts where city = " & "'" & var1 & "'"
any suggestions?

Try this:

*sqlstr = "Select * from contacts WHERE CITY = '" & var1 _
*& "' AND TYPE='" & var2 & "';"

I have to warn you that this kind of query is very dangerous because users
can include '-characters to the query and make SQL injections to your
database.

*-Teemu
Hi Teemu,

Thanks, it works! :)
Jan 2 '08 #15
On Jan 1, 6:15 pm, "Cor Ligthert[MVP]" <notmyfirstn...@planet.nl>
wrote:
Rafii,

First try to use the right names for the right things. By instance, don't
talke about a datagrid while you use a datagridview in your code. Those are
in fact two complete different kind of grids.

To review your code (including your question)
>Dim sconn As New SqlConnection()
sconn.ConnectionString = "Data Source=(my.Server;Initial
Catalog=MyDB;Persist Security Info=True;User ID=sa;Password=Mypass"
Dim filterresults As New SqlDataAdapter("Select * from contacts ", sconn)

Try to keep the name, this is not a filterresult, this is a construction of
a DataAdapter.

This can be including your where clause

Dim da As New SqlDataAdapter("Select * from contacts WHERE CITY=@VARIABLE1
AND TYPE=@VARIABLE2", sconn)

I have let it, however it is never right to use a dataname as "Type", I
would in your place give that in your database and in your code another
name.
>Dim contactsresult As New DataSet("result")
If Not IsNothing(filterresults) Then

The DataAdapter is never nothing, you have constructed it above therefore
this if is complete without any sence. Therefore you can remove it.

For selection in a select string it is for more than one reason good to use
parameters. It makes at least your code more visible and easy to handle.

da.SelectCommand.Parameters.Clear()
'The code above is in fact only needed all second times, however it does not
bother to place it in that instead an if or whatever more costly instruction
than the one above.

da.SelectCommand.Parameters.Add(New SqlParameter("@Variable1",
TheCityField))
da.SelectCommand.Parameters.Add(New SqlParameter("@Variable2",
TheTypeField))
>filterresults.Fill(contactsresult)

this one I have changed in
da.Fill(contactresult) 'filterresult is in my idea a confusing answer it is
a result of the fill (at SQL side the name is ResultSet
>filterresults.Dispose()

This dispose does nothing, the dispose is there because it is inherrited
from components, but that does not mean that you should use it.
>End If

The End If above can go of course too away because of the removing of the if

Me.ContactsDataGridView.DataSource = contactsresult.Tables(0)

It would be better to set all this code inside a Try and Catch block,
however that would make it in my idea to confusing to tell it direct.

Cor
Cor hi,
Truly your reply was more than I expected to receive.
I understand that there is more than one method to do things
And for sure, some ways are more corrective to certain environments
And certain features.
The truth is, I started off with VS2005 just 3 weeks ago
And I'm still experimenting. I have no programming experience
And I use mostly snippets that are provided with VS. Some are more
Easy to understand these are the ones I use.

I've seen the "Try and Catch" codes in some samples, but I didn't
figure them
Out yet.
As for ParamSQL I do think it's better but I still need to understand
how to work with it.. (Any references?)

Finally, security issue...
The program I build is for my own needs. That's the reason I installed
VS to start with. I needed a solution quick and fast. The bottom line
of the software is to insert contacts to database and then to be able
to filter them out and send them an email using predefined email with
outlook.
Still don't know how I will ever figure out how to send the email...
while collecting all the filter results
Still don't know how I will ever figure out how to debug the
application..
For example, how to start with clear combo boxes (which are data
bound) when the
Application kicks off...and why the first value that appear in the combo
box disappear after I choose another value...

So what I'm trying to say basically, is thanks for the tips ļ
Jan 2 '08 #16
It wasn't the message I was referring to Tom. It was the degree of language.

'... it is VERY bad practice. It opens you up to quoting issues, and more
importantly security issues.'

'it is VERY bad practice' is a very negative phrase (like a kick in the
teeth).

'quoting issues' or more correctly, 'escaping apostrophes' is a bread and
butter coding technique that shouldn't even rate a mention.

'security issues' is too broad a phrase to go unqualified. Yes there is a
risk but degree of risk completely depends on the situation and no two are
ever the same.

Don't get me wrong, I am a fan of using good practices but I was never a fan
of inventing convoluted code purely to avoid using the 'dreaded GOTO.

As everybody is well aware, it doesn't take much for an innocuous phrase to
cause a thread to escalate into a flame war and the use of more appropriate
language can go an awful long way to avoiding those situations.
"Tom Shelton" <to*********@YOUKNOWTHEDRILLcomcast.netwrote in message
news:uV**************@TK2MSFTNGP02.phx.gbl...
On 2008-01-02, Stephany Young <noone@localhostwrote:
>While I, personally, would not use concatenation unless there was no
practical alternative and/or it was in a 'safe' environment, let's put
things in perspective and moderate your warning say that there could be a
security risk.

The significance of that risk depends on a number of factors that all
have
to 'come together' simultaneously to allow a 'SQL injection attack'.

As for the other issues, (quoting, dates/times, formatting, etc.), they
are
bread and butter issues and don't cause concatenation to be 'bad'.

Let's start off 2008 by encouraging people to use good practices rather
than
kicking them in the teeth for using a practice that some MIGHT consider
to
be 'bad'.

That's exactly what I was doing. I was warning him of the potential
risks of using concatenation to form his query strings. I don't believe
my warning was of a dire or overly sentationalist nature. It is a bad
practice - even in "safe" environments.

--
Tom Shelton
Jan 2 '08 #17
On Jan 2, 12:06*am, "Stephany Young" <noone@localhostwrote:
It wasn't the message I was referring to Tom. It was the degree of language.

'... it is VERY bad practice. *It opens you up to quoting issues, and more
importantly security issues.'

'it is VERY bad practice' is a very negative phrase (like a kick in the
teeth).

'quoting issues' or more correctly, 'escaping apostrophes' is a bread and
butter coding technique that shouldn't even rate a mention.

'security issues' is too broad a phrase to go unqualified. Yes there is a
risk but degree of risk completely depends on the situation and no two are
ever the same.

Don't get me wrong, I am a fan of using good practices but I was never a fan
of inventing convoluted code purely to avoid using the 'dreaded GOTO.

As everybody is well aware, it doesn't take much for an innocuous phrase to
cause a thread to escalate into a flame war and the use of more appropriate
language can go an awful long way to avoiding those situations.
I'm sorry if the tone and content of my warning displeased you. But,
I felt that it was important to point the OP to what is considered by
many to be a best practice method. You are correct in that building
dynamic queries in that way is not always a problem - but why not
learn and use the safe method right from the begining? And if you
want my honest opinion, using concatenation results in uglier and
harder to read code (especially as your queries become much more
complex).

I don't think this needs to turn into a flame war, nor does it have to
turn into a long drawn out thread. I think you and I have both made
our positions clear on the issue. Happy 2008 to you!

--
Tom Shelton
Jan 2 '08 #18

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Eugenio | last post: by
3 posts views Thread by Harvey | last post: by
6 posts views Thread by Martin Lacoste | last post: by
reply views Thread by Jim Kennedy | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.