Prevent apostrophe from being inserted in INSERT statement

[nicks60610]

I have a few ASP pages where I insert and update data in to an Oracle 8 database, but the problem I have now is when a user enters "free" data in text fields and they would enter an apostrophe or other "illegal" character, they receive an error message after they submit the form since Oracle cannot handle the data. How can I easily prevent this in a given field since I don't know what they will be entering in the field?

Thanks in advance,

A Beginner in Oracle

 

[SantaMufasa]

Nicks,

I personally use Oracle's TRANSLATE function to replace "illegal" characters with legal characters (such as a back-quote [`] to replace a regular single-quote ['], et cetera).

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:46 (20Jan04) GMT, 11:46 (20Jan04) Mountain Time)

 

[carp]

Or you can use the REPLACE function if you need to replace a single character with multiple characters (such as apostrophes).

 

[nicks60610]

But wouldn't the replace function assume I know what will be in the statement? I.e. the statement would be written like this: replace (string1, string_to_replace, [replacement_string] )

So then my statement would look like:

replace ('',','') ?? Since I am not sure what will be in the actual field other than some text where someone may have written O'Donnell instead of ODonnell...

pardon my ignorance here, but I am but a newbie with this : - )

 

[Turkbear]

Hi,
You probably would have to test for all the 'illegal' characters -
( Test against the ASCII value ranges, maybe, before submitting the Sql statement)

Note: It can be dangerous to allow totally free text insertion, since a very clever user may be able to use 'Sql Injection' to get into the database without any constraints.

Here is a link to a .pdf paper on the problem.

http://www.spidynamics.com/papers/SQLInjectionWhitePaper.pdf

 

[nicks60610]

I found this elsewhere and so far it is working for one of my fields but not both??

This is the code in place before the insert statement:

If LEN(TRIM(Remarks)) > 0 Then
Remarks = Replace(Remarks, "'","''&quot
End If

But I can only get it to work in one field?

 

[carp]

Yes - only one field per REPLACE function. But your question was "How can I easily prevent this in a given field ..." so this should not be a problem.

 

[sem]

I think that the salt of this question is apostrophe (quote character) rather than invalid character. This specific one is completely valid but due to it's usage as a string delimiter it should be "escaped", or to be more specific, it should be doubled.

insert into names values ('O''Brien')

statement inserts O'Brien into names table.

Regards, Dima

http://seminihin.narod.ru

 

[nicks60610]

Well, the issue is too I don't know what the user will enter in this field, so there is no specific insert "xxxxxyyyy" to fieldname, but rather insert fieldname, so whatever is entered in to the field name is what is being inserted and I need to prevent the error when someone enters a comma (,) or apostrophe (') or a semicolon (.

 

[nicks60610]

I found it - the fieldname I was refering to in the LEN statement was wrong, this is what happens when you are up late the night before, hehehehe. I made the corrections and it is working like charm. Thanks for all your help!!!

 

[sem]

The issue is that you probably build sql statement on fly instead of using prepared one and just binding variables before each execution

Regards, Dima

http://seminihin.narod.ru