Escape Character Not Working

Zarathu · #1 (**permalink**) 11-03-06, 17:08

Code:

INSERT INTO `textpattern` VALUES (18,'2006-04-23 22:18:00','zarathu','2006-10-22 23:10:27','zarathu','Rednecks','','That\'s right.\r\n\r\nThese filthy rednecks think they\'re having the time of their lives while . . . .

Look closer.

Code:

That\'s

Code:

they\'re

For a rather sickening reason that is still unknown to me, those escape characters to show that there is a single quote there is specifically NOT working the way it should. Therefore, it screws up the entire statement.

Help.

shammat · #2 (**permalink**) 11-03-06, 20:09

Not 100% sure about MySQL but the ANSI standard for embedding single quotes in a character literal is two single quotes:
... VALUES ( 'That''s working' )

Zarathu · #3 (**permalink**) 11-03-06, 20:12

Quote:

Originally Posted by shammat

Not 100% sure about MySQL but the ANSI standard for embedding single quotes in a character literal is two single quotes:
... VALUES ( 'That''s working' )

Hmm... Can anybody else confirm this?

r937 · #4 (**permalink**) 11-04-06, 04:43

i can confirm that that is the standard sql way of escaping the single quote embedded in a string (delimited by single quotes)

i can also confirm that mysql supports it

Zarathu · #5 (**permalink**) 11-04-06, 11:34

Quote:

Originally Posted by r937

i can confirm that that is the standard sql way of escaping the single quote embedded in a string (delimited by single quotes)

i can also confirm that mysql supports it

Alright, so I should replace all:

\'

with:

''

^ The above is two single-quotes.

I'll update if I have any problems.

Zarathu · #6 (**permalink**) 11-04-06, 11:50

Another question--does this apply for double-quotes? The SQL exporter used \" for them, but when I removed the \ character, nothing was affected.

r937 · #7 (**permalink**) 11-04-06, 17:05

double quotes are used only within strings...

Code:

insert into comments (comment) 
values ( '"Oh, no," said Todd. "That''s what she said."' )

double quotes are also the standard sql quoted identifier delimiter ...

Code:

select count(*) as "Total Rows" from ...

Internet Services

Web Development

Digital Marketing

Consumer Tech