Replacing an apostrophe in a string

Question

I have two databases. One has apostrophe in names like O'Bannon and one does not. I need to merge them and find the duplicates. Since it's harder to add the apostrophes I'm tring to remove them instead

But this...

UPDATE Client
SET Last_Name = REPLACE(''','')

Clearly won't work. How does one escape the '.

I'm using Xojo (not PHP)

You don't want OBannon, you want O'Bannon. Databases are perfectly capable of storing apostrophes! — Rich Bradshaw, May 17 '13 at 21:38
Or use two single quotes: `REPLACE('''', '')`. For what it's worth, Oracle and SQL Server support `''` but not `\'`. — Ed Gibbs, May 17 '13 at 21:40
If "O'Bannon" is killing your code, does this "a';DROP TABLE Client; --" also kill your code? If so the problem is somewhere else :) — Honza Brabec, May 17 '13 at 21:50
@JVMX If my name was O'Bannon and you would put me as OBannon in your client database, I would stop being your client. If you are unable to store my name correctly, I wust that you are able to store anything properly such as credit card data etc. — glglgl, May 17 '13 at 22:55
`a';DROP TABLE Client; --` only kills the database, the code is all right. ;) — Jens Erat, May 20 '13 at 01:08

score 5 · Accepted Answer · answered May 17 '13 at 21:44

Like you say, you'll want to escape quote characters.

See this documentation on string literals:

There are several ways to include quote characters within a string:

A “'” inside a string quoted with “'” may be written as “''”.

A “"” inside a string quoted with “"” may be written as “""”.

Precede the quote character by an escape character (“\”).

A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.

Depending on how you're dealing with the SQL, though, you may need to do more than that. If the application is escaping the quote character, and passing that to a stored procedure call, you may run into the same issue if you are not using parameter binding with prepared statements. This is due to MySQL removing the escape character upon processing the inputs of the SP. Then the unsantized character makes its way to the query construction and the problem repeats itself if it should be escaped there. In this case, you'll want to switch to parameter binding, so that the escaping and query construction is out of your hands.

For more information about SQL injection, see [this great answer by Bill Karwin](http://stackoverflow.com/a/306818/2272443), and [this slideshow he put together](http://www.slideshare.net/billkarwin/sql-injection-myths-and-fallacies). — Erin Schoonover, May 21 '13 at 18:51

score 4 · Answer 2 · answered May 17 '13 at 21:41

4

Here we go:

UPDATE Client SET Last_Name = REPLACE(Last_Name, '\'', '');

You just need to escape apostrophe will backslash .

answered May 17 '13 at 21:41

Sumoanand

8,835
2
47
46

Ivaylo Strandjev · Answer 3 · 2013-05-17T23:25:51.090

3

Simply add an escape character(\) in front of the quote:

SET Last_Name = REPLACE('\'','')

Still I don't think this is the right way to go as you will lose the information for the original name of the person and so o'reily and oreily will seem to be the same surname to you.

edited May 17 '13 at 23:25

answered May 17 '13 at 21:37

Ivaylo Strandjev

69,226
18
123
176

They won't loose information, they would lose it, which is even worse. – glglgl May 17 '13 at 22:56

score 1 · Answer 4 · answered May 17 '13 at 21:37

From 9.1.1 String Literals

Table 9.1. Special Character Escape Sequences

Escape Sequence Character Represented by Sequence
\0  An ASCII NUL (0x00) character.
\'  A single quote (“'”) character.
\"  A double quote (“"”) character.
\b  A backspace character.
\n  A newline (linefeed) character.
\r  A carriage return character.
\t  A tab character.
\Z  ASCII 26 (Control+Z). See note following the table.
\\  A backslash (“\”) character.
\%  A “%” character. See note following the table.
\_  A “_” character. See note following the table.

Of course if ANSI_MODE is not enabled you could use double quotes

I have two databases that I had nothing to do with the creation of. One has the apostrophe one does not. I need to merge them and find the duplicates. If you could write me some code that puts them in where they are missing I would gladly take all the 'don't change the name' comments to heart.... — JVMX, May 20 '13 at 16:44

score 0 · Answer 5 · answered Feb 03 '14 at 08:20

0

If in case you are just looking to select, i.e., to match a field with data containing apostrophe.

SELECT PhraseId FROM Phrase WHERE Text =  REPLACE("don't", "\'", "''")

answered Feb 03 '14 at 08:20

Thiru

1,380
13
21

Replacing an apostrophe in a string

5 Answers5