louis99

Hi, I am new here

I have a text box, I put data from text box into a database.

For example, if I put the word That in text box. The query is successfully executed. But if I put That's then it will fails because a ' will causes the error.

What should I do to take care of that?

thanks

iignotus

Not sure about VB, but do you have to escape quotes?
instead of just.

__________________

bl00dninja

not really sure about your implementation here..is it just a coincidence that that particular charcter is also the one that designates comments in VB?

__________________
i put on my robe and wizard hat...

Have you ever heard of Plato, Aristotle, Socrates?...Morons.

InfoGeek

If you're executing a SQL insert query, then you need to escape the single quote(') by preceding it with another single quote(').

__________________
PFO - My daily dose of technology.

Rory

Yeah it's your sanitization of input that's the problem. Basically whatever language, if you're taking user input and shoving it into a command of some form, be it ping.exe or sql, there's always the possibilities of errors or even code injection if you do not correctly constrain, sanitize and or reject user input before forming your command string. For example, I could put an SQL comment "--" in your textbox and depending what you were using wipe the database, access confidential information or even (say in access) use the stored procedure xp_command to execute arbitrary commands as SYSTEM (==root). OK, not a big concern for this project you might think, but you'd appreciate the importance of big_k stopping people because they have strange usernames executing code on the pfo servers.

Constrain, reject, sanitize!

In this instance, you should write a function that validates a token in an SQL statement correctly, in every possible way you can think of. Length, data type, range, format. If in doubt, reject the input. OK, so now you've got it escaping the quote with a slash or whatever. What about if I told you that the big-endian extended characters c0af e080af 252f 35c 253563 3563 255c u005c are all various unicode incarnation of the back/forward slash that Microsoft miserably failed to validate in IIS4, leading to trivial URL directory traversal attacks (microsoft.com/..%c0%af..%c0%af..%c0%af..%c0%af../windows/system32/crash.exe) that could let an attacker manipulate and run arbitrary files on the server.

This is why personally I avoid taking input that has come from the user and directly transferring it into something else like an SQL string: even if it's just the other side of a select statement, by not copying it directly you are eliminating the chance that some new extension to the SQL language that uses the # character as an escape sequence will not cause a security hole. Nevertheless, if you absolutely must, their are wrapper libraries or alternatively a good selection of regular expressions that "clean" stuff ready for SQL concatanization.

</rant>

DaWei

Great post, Rory. My frequently-increasing despair is salved by such.

__________________
Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code.
Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers