Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL Server chars as literals 1
- Thread starter keenanbr
- Start date
-
1
- #2
Centerfuge
Programmer
Unfortunately mmilan has it totally right. There is not much else you can do.
No offense, mmilan, but God, I'm tired of hearing "just double up the apostrophes" in response to these questions.
Doing this will leave your program open to a serious attack called SQL Injection. This vulnerability allows an attacker to do anything they want on your server, including formatting the C: drive. And all he has to do is fill out a field in your application correctly.
Read:
To avoid this, you need to use ADO parameters. Not only will this solve your apostrophe problems, but it will also prevent SQL Injection attacks, and even makes your program run faster!
Chip H.
____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
Doing this will leave your program open to a serious attack called SQL Injection. This vulnerability allows an attacker to do anything they want on your server, including formatting the C: drive. And all he has to do is fill out a field in your application correctly.
Read:
To avoid this, you need to use ADO parameters. Not only will this solve your apostrophe problems, but it will also prevent SQL Injection attacks, and even makes your program run faster!
Chip H.
____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
No offence at all Chip - your post was constructive, and therefore most welcome...
I didn't consider SQL injection, so nice catch...
However, the OP is wanting to deal with character literals - and that's the question I answered. I agree that in an ideal world all modification of code would be carried out under stored procedures, but for many of us who have walked into someone else's code pile, that's simply not an option.
I suggest OP stars chip's post.
mmilan
I didn't consider SQL injection, so nice catch...
However, the OP is wanting to deal with character literals - and that's the question I answered. I agree that in an ideal world all modification of code would be carried out under stored procedures, but for many of us who have walked into someone else's code pile, that's simply not an option.
I suggest OP stars chip's post.
mmilan
Centerfuge
Programmer
Just so you guys know the "Unfortunately" was in response to it being a shame that in dealings with character literals that there isn't much else to do. 
... you mean except to use ADO parameters in a command object, or use a parameter SP ![[smile]](/data/assets/smilies/smile.gif "[smile] [smile]")
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
for steam enthusiasts
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first
'If we're supposed to work in Hex, why have we only got A fingers?'
for steam enthusiasts
JohnYingling
Programmer
I thought parameters could be used even without a Stored Procedure so long as the Data Statement in CommandText specified parameters using the @.
Forms/Controls Resizing/Tabbing
Compare Code
Generate Sort Class in VB
Check To MS)
Forms/Controls Resizing/Tabbing
Compare Code
Generate Sort Class in VB
Check To MS)
Yes, you can use ADO parameters with ordinary SQL statements. I think you use question-marks as placeholders in that case.
(I've flipped between languages so often recently that I'm getting whiplash!)
Chip H.
____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
(I've flipped between languages so often recently that I'm getting whiplash!)
Chip H.
____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
- Thread starter
- #14
There are still a few times where you can't use parameters. A SQL "IN" clause is one of them. But otherwise, using parameters (while a lot more coding) will result in faster program execution because the database can put your statement in it's procedure cache. The next time you ask for it to be run, the database can pull it out of cache, and doesn't have to parse your SQL to check for validity (do metadata lookups to make sure all the columns & tables exist, check for syntax errors, etc), since it's already done that before.
Chip H.
____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
Chip H.
____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
- Status
Similar threads
