Our tip about substituting for apostrophes in a SQL query provoked a storm of responses. Nay, a veritable blizzard of responses. Many of them took your tips guy to task for that bit of wisdom, but most of them said that we should have just used the replace function instead. So in the spirit of community building, we offer these folks' responses as this week's tip.
This from Tom Johnston:
Bruce Wood offered a work around for updating database fields with apostrophe's by substituting any chr$(39) with chr$(96) (backwards apostrophe). Wouldn't you rather keep the characters you originally typed in? SQL query errors can result from updating a field with either the apostrophe or the quote (") symbol, depending on what your system is using as a field delimiter. If your system is using apostrophes as a field delimiter, apostrophes within the field will cause an error. Likewise, if your system is using quotes as a field delimiter, quotes within the field will also cause an error. So how did fields with quotes & apostrophe's get in the database in the first place? SQL does provide for this. Simply double up on any apostrophe found in the field (if delimiting with apostrophe's), or double up any quote found (if delimiting with quotes), prior to updating the field. For example, if you are adding a text value to a database:
The database tries to send 'Joe's Stuff' to the SQL server. The odd number of apostrophes returns an error. But if you update it first to:
Guess what? Updating the database now updates the field with the original entry = Joe's Stuff
I've supplied a function below that will return an updating text string, prepared for SQL update. It accepts two arguments, the original string, and whatever your field delim character is (quote=chr$(34) or apostrophe=chr$(39)). It returns a string that has double of any found instance of the 2nd argument you provide.
Code: Function DBCheckDelim(ByVal dstr, ByVal delimchar) dlen = Len(dstr) p = 0 Do p = p + 1 If p > dlen Then Exit Do If Mid$(dstr, p, 1) = delimchar Then dstr = Left$(dstr, p) & delimchar & Right$(dstr, Len(dstr) - p) dlen = dlen + 1 p = p + 1 End If Loop DBCheckDelim = dstr End Function
Not to be outdone, Christopher Nachand offers his approach:
In a SQL query, sometimes you cannot capture the rogue single apostrophe during data entry. This is especially true when dealing with data transfer or conversion applications, and it's usually found in people's last names. You can use a fairly simple function to look for the single quote and replace it with two single quotes, which most databases will recognize as a single quote character for display purposes.
Code: Public Function EncodeString(ByVal str As String) As String ' Replaces quote characters (') with two quote characters so that the string may be used in a query as a literal Dim strResult As String Dim x As Long For x = 1 To Len(str) If Mid$(str, x, 1) = "'" Then strResult = strResult & "''" Else strResult = strResult & Mid$(str, x, 1) End If Next x EncodeString = strResult End Function
...a variation on the same theme.
Jesse Waldrip offers a slightly longer variation.
When saving info to a database table the apostrophe character causes run-time errors. Most databases do not allow the use of the apostrophe as it is interpreted as a new field starting. This will cause syntax errors. Here is a very simple function that overcomes this problem by handling it when data is saved to an Access or SQL Server database.
Public Function QuoteHandler(ByVal sRaw As String) As String 'given a text string, look for single quotes. for every single 'quote found, place another single quote next to it. This allows 'these strings to be processed when handed off to SQL Server/Access Dim iQuote As Integer, iLen As Integer iQuote = InStr(sRaw, "'") Do While iQuote iLen = Len(sRaw) sRaw = Left$(sRaw, iQuote) & "'" & Right$(sRaw, iLen - iQuote) iQuote = InStr(iQuote + 2, sRaw, "'") Loop QuoteHandler = sRaw End Function
Greg Obleshchuk offers a solution based on the Replace function, as did a number of readers.
Bruce Wood's tip about replacing the ' with a ` in a SQL query sounds good -- but it is bad, very bad. The user would always try and turn the ` back into the '. Most databases handle the ' very well. The code below could be used instead. You could use this when you assign the textbox text to the field. If you are using bound textboxes, place it in the beforeupdate events. This tip will double '' and "" so all things will work.
Code: rs.field("Name").value = Replace(Replace(Text1.Text, "'", "''"), """", """""")
Another Replace tip, this from John Barone
You published a tip dealing with the apostrophe in a SQL query and suggested to use the KeyPress event to replace the apostrophe with a backwards apostrophe.
You can deal with it, more simply IMHO, by using the Replace function. It doesn't even add an extra line (really); can't get easier than that!
'An example of how to use Replace
Dim strSQL as String 'Assume that value is being appended from Textbox1 to SQL string strSQL = "SELECT...WHERE = '" & Replace$(Textbox1.Text, "'", "''") & "'" 'Replace one apostrophe with two so SQL works!
This tip is generated from SearchVB.com user Randy N. Ligsay:
Tip: the function below validates a SQL query string containing reserved characters such as single quoted('), double quotes(""), etc., which will cause runtime-error when saving it to database. To avoid such problems, the function doubles that reserved character to override the use of that character.
e.g. oracle backends
input : ILOVE'VBPROG - you can't save this to a database, because oracle treats the single quotes as a string encloser, but you can save this string without any change in value by using the function below. Pass the string to the function, and the resulting output would be ILOVE''VBPROG. This overrides the usage of the single quote for this particular back end (the single quote was doubled). This function is also able support other reserved character. Use the StrSearchString param of the function.
sStr = "ILOVE'VBPROG" -------------------------- Call this function as you save your data to a database file. See sample below INSERT INTO DUMMYTABLE(WhatILove)VALUES('" & ValFldChr(sStr,"'") & "')" -------------------------------------- Function ValFldChr(sString as String, sStrSearchString as String) as String ValFldChr= sString If sString = "" or StrSearchString="" Then Exit Function ValFldChr= REPLACE(sString, sStrSearchString, sStrSearchString & sStrSearchString) End Function
Finally, this was the first response we got, the one that started this magnum opus on its way to you. This is from Martin Gravel.
I received a tip&trick mail this morning. It was submitted by a fellow named Bruce Wood, and was for fixing apostrophes in database fields. His solution was to replace all apostrophes with the open quote character. How can you guys publish such a nuisance? If a user needs an apostrophe in a field, we, as programmers, have to let the user put an apostrophe in the field and process it correctly! O'brian is not the same as O`Brian
We asked Martin what his solution is for this type of SQL query, and here is his response.
The programmer should allow the quotes in the input textbox. Once he is ready to send the data to the SQL server, the programmer should use the REPLACE function to pre-process and thus hide special characters.
Let's say the txtInputName textbox contains "O'Hara". If the SQL string is " Update members set lastname=' " & txtInputName & " ' " , then you will receive a SQL error. But if you use the REPLACE function, you can have " Update members set lastname=' " & Replace(txtInputName,"'","''") & " ' ".
The REPLACE function will replace each occurrence of single quote by a pair of single quotes. SQL will accept this syntax and when encountering the single quotes pair, will drop one and include the other in the string itself. You still need starting and ending single quotes to enclose the whole string.
This method has been widely documented in different forums for several months. Programmers use it more and more as it is very simple and powerful.
Please read the tip that started it all, 'Darn that apostrophe'