Problem with characters like ' (single quotes) in the query

[m_asu]

Hi All,

I have a discussion forum application through which users submit messages. Users write in the text box, which is then processed by Java into a String query and then executeQuery puts it into MS ACcess database.

My question:

Some characters like ' (single quotes) can screw up the SQL query syntaxt and then the user can get an error and the message may never be posted. I have taken care of the single quote as follows: (assume String variable 'data' represents the message to be posted).
data = data.replaceAll("'", "''");

What this basically does replaces all the single quotes by 2 single quotes each. Thus the query syntax will by maintained. Are there any other characters which can screw up the SQL syntax and which I may have to replace before processing the query?

Thanks in advance.

m_asu

 

[Rob.Mills]

# could cause a problem as this is what you use in SQL to surround dates.

 

[m_asu]

dates in a string

But I am sure that if the date is part of the message String, then it wont cause any problems.

Example: If user tries to post:

My birthday is same as your's: i.e. 01/01/78

---

If above message is being put into String variable and then into SQL, the only manipulation that I will have to do before make it into an SQL statement is that I replace ' with ''.

Right? Or date will also make create complications?

If I am comparing dates then I will have to use #. not otherwise.
right?

thanks in advance.

m_asu

 

[Rob.Mills]

You're confusing me. Are you saying that you're taking a complete message and putting it in the Where clause of the SQL statement? Show me your SQL if you don't mind.

 

[Happy YN]

M_asu
I am having the identical problem you mentioned with single quotes
Trouble is your solution is neat but not enough for me as I need to have the string exactly as the user enters it without replacing single quotes with double
Is there any other way ? e.g to surround that part of the sql with brackets etc so that it takes all the text as one string?
Or a string in a string?
Thanks

 

[Jon K]

Happy
As the double quote is less likely to occur in text strings, you can use the double quote as the text delimiter, e.g. instead of using,

SQL = "Select * from Table1 where Description ='" & Me.txtSearchString & "'"

you can use,
SQL = "Select * from Table1 where Description =""" & Me.txtSearchString & """"

 

[Happy YN]

Thanks Jon to my rescue again.
Can you be more specific. It stops on the line
If (DCount("*", "tblprinter", "[strprinter] = '" & strPrinterNew & " '")) > 0 Then

strprinternew is the text with the single quote
So how do I replace it
Thanks

 

[Jon K]

If (DCount("*", "tblprinter", "[strprinter] = """ & strPrinterNew & """")) > 0 Then


i.e. replace each single quote with two double quotes.

 

[Happy YN]

Thanks Jon that works a treat
Is that always true i.e. One can replace a single quote with 2double?

 

[Jon K]

Yes, we can always use the double quotes as the text delimiter instead of the normal single quotes. Just replace each of the normal single quotes with two consecutive double quotes.

 

[Happy YN]

Thanks a million!