Illegal Chars in SQL?

  • Thread starter Thread starter szabelin
  • Start date Start date
S

szabelin

Hello - this might be more of sql syntax question but I
hope to an answer in this newsgroup. If I write a string
that contains char ' ado.net throws exception. At this
point I filter for " ' < > (these cause conflict in my
asp page).

My question is what is the complete list of characters
that can not be a part of sql string besides ' , if any.

Thanks
 
In SQL ' delimits a string. If it needs to be part of a string, then you
need to use two of them, which then tells the parser that it is actually a
single quote character in the text.

As for < >, I'm not sure how that relates to SQL, or why it is a problem for
asp.net. It sound like you may be just writing scripts in the .aspx itself,
which you shouldn't be doing - you should use the codebehind. But as long
as instead of "
Dim str = "<script>"
your write:

Dim str = "<script" + ">",

you should be fine.
 
This is an age-old issue. Use Parameters to construct your queries. ADO.NET
(and ADO classic) can deal with the framing quotes for you.

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Bill:

I had an idea to start lobbying MS to take out the ability to use single
quote characters in SQL. I've heard many o person (not in this thread, just
in general) berate using Parameters because that's not the way they used to
do it, and then come across a GOOD Irish Name (and there aren't any other
types) like O'Flynn and then have to write Replace code or some other
kluge..I'm going to scream. Maybe I'll write a command object that will
spit out any single quotes ;-).
 
And then write Readers and DataRows. And include % for
like clauses as well. Hard to believe MS hasn't already
done that ? ;))))))
 
Building up SQL in concatenated strings (the way you might be used to doing
it) is dangerous as it leads to SQL injection attacks. Using Parameter
queries also deals with date parameter issues and leads to more efficient
queries--not to mention the ability to deal with what I call the "O'Malley"
issue. SQL is not Microsoft's "fault"--they conform to ANSI specifications.
The % issue falls into the same category. Trying to get them to drop single
quotes would be like trying to get Ireland to stop brewing Guinness. ;)

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Thanks a lot Bill - you are making a good point. No, I
wasn't using Parameters in some of my hand-written sql
select queries.
 
Well that's out of the question, Guiness is just too good. I was pretty
much kidding around....but it just seems like there's all downside and no
upside with dynamic SQL.

Thanks again,

Bill
 
Back
Top