Issues with protected words as values in upload 1

[nedstar1]

Hi folks,

I'm trying to upload some user data to my db, and I've got a few odd entries - some of my first names have apostrophes, and it's throwing off my INSERT - do you frame it with \ \ like 'D'Juan' becomes '\D'Juan\' - is that right?

More to the topic, I have a user with the last name Grant, and in my upload, it keeps erroring here as well.

A sample line to pick apart:

INSERT INTO `tbl_users` (`firstName`, `lastName`, `username`, `pwd`) VALUES('Tony','G***','tony.g***@*****.com','G*****');

Asterisks added to protect the innocent.

I'd be happy to RTFM if someone will point me to the page . . .

Regards,
Nedstar1

 

[azzazzello]

I believe MySQL quote() function will solve all your problems. So...

... VALUES(quote(Tony),quote(G***),....)

read about it here

http://dev.mysql.com/doc/mysql/en/string-functions.html

 

[TonyGroves]

You have several options:

(1) Use double-quotes instead of apostrophes (if you're sure there are no double-quotes in the names):[tt]
VALUES ("O'Reilly")[/tt]
(2) Escape the apostrophes with back-slashes:[tt]
VALUES ('O\'Reilly')[/tt]
(3) Escape the apostrophes with double-apostrophes:[tt]
VALUES ('O''Reilly')[/tt]

The QUOTE function wouldn't work here, as a constant argument still has to be quoted and properly escaped.




(I'm not that innocent, am I?)

 

[nedstar1]

Hi guys,

Thanks for the tips - I'll read about the quote thing this morning. I was looking for the propoer syntax for backslahses - I had tried to figure it out by reducing my INSERT, but I was having trouble remembering how they are used. I think I'll just use the double quotes - I was hoping to avoid rebuilding my SQL, but oh well . . .

Thanks a bunch, folks. Problem solved.

Regards,
Nedstar1