MySQL: escape special characters

Hi All,

Is there a function (or some way to) replace special characters in MySQL e.g. quotes, commas, carriage returns etc...

It's for an INSET INTO query.  So i'm hoping for a REGEX or similar that can be applied to the field.

Something along these lines
SELECT id, addslashes(company), addslashes(contact) FROM crm;

Open in new window


Many thanks
D
LVL 2
detox1978Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

stu215Systems AnalystCommented:
This article  shows all the special characters with the escape strings:
http://dev.mysql.com/doc/refman/5.0/en/string-literals.html
detox1978Author Commented:
So how do I apply that to a basic SELECT query?  The below query doesnt work

SELECT id, mysql_real_escape_string(company), mysql_real_escape_string(contact) FROM crm;

Open in new window

stu215Systems AnalystCommented:
This article shows how to use regular expressions in MySQL which combined with the escape strings should do what you need.
http://dev.mysql.com/doc/refman/5.0/en/regexp.html
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

GaryCommented:
You only escape the values you are passing to mysql

"select * from table where column='" . mysql_real_escape_string($the_value) . "'";

"insert into table (column1, column2) values ('".mysql_real_escape_string($the_value1)."','".mysql_real_escape_string($the_value2)."')";

Open in new window


Though the old mysql library is now deprecated and you should be using MySQLi or PDO
stu215Systems AnalystCommented:
You shouldn't need escape strings in a select statement, only on INSERTS or UPDATES.

INSERT INTO crm (id, company, contact)
VALUES (mysql_real_escape_string($val1), mysql_real_escape_string($val2), mysql_real_escape_string($val3))

Open in new window


Assuming you wanted to use REGEX:
INSERT INTO crm (id, company, contact)
VALUES ('$val1' REGEX 'expresion1', '$val2' REGEX 'expression2', '$val3' REGEX 'expression3')

Open in new window

detox1978Author Commented:
A little background on what I am doing;

I have an ODBC connection that imports data from Lotus Notes.  The table doesnt have an auto_id etc... and is packed with carriage returns and single/double quotes.

I can do a simple INSERT INTO to get the auto id, but I'd like to remove carriage returns and escape any other special characters.

When I tried  mysql_real_escape_string() it returns the following error.  Function does not exist.
detox1978Author Commented:
This is a scheduled task being run on the MySQL server (not via a PHP script)
GaryCommented:
mysql_real_escape_string() is a PHP function,not a MySQL function

So how exactly are you getting the data and inserting it - you must be using some language.
detox1978Author Commented:
It's native mySQL script run via SQLyog on a scheduled task.
GaryCommented:
Well I would suggest you convert to using PHP and set it up as a cron job/scheduled task

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
detox1978Author Commented:
Thanks, I thought there would be something built into MySQl.  I've done it via a PHP script.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.