Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL:  escape special characters

Posted on 2014-12-02
12
Medium Priority
?
1,577 Views
Last Modified: 2014-12-02
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
0
Comment
Question by:detox1978
  • 5
  • 3
  • 3
12 Comments
 
LVL 10

Expert Comment

by:stu215
ID: 40476342
This article  shows all the special characters with the escape strings:
http://dev.mysql.com/doc/refman/5.0/en/string-literals.html
0
 
LVL 2

Author Comment

by:detox1978
ID: 40476359
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

0
 
LVL 10

Expert Comment

by:stu215
ID: 40476386
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 58

Expert Comment

by:Gary
ID: 40476390
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
0
 
LVL 10

Expert Comment

by:stu215
ID: 40476414
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

0
 
LVL 2

Author Comment

by:detox1978
ID: 40476454
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.
0
 
LVL 2

Author Comment

by:detox1978
ID: 40476467
This is a scheduled task being run on the MySQL server (not via a PHP script)
0
 
LVL 58

Expert Comment

by:Gary
ID: 40476484
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.
0
 
LVL 2

Author Comment

by:detox1978
ID: 40476531
It's native mySQL script run via SQLyog on a scheduled task.
0
 
LVL 58

Accepted Solution

by:
Gary earned 2000 total points
ID: 40476545
Well I would suggest you convert to using PHP and set it up as a cron job/scheduled task
0
 
LVL 2

Author Closing Comment

by:detox1978
ID: 40476581
Thanks, I thought there would be something built into MySQl.  I've done it via a PHP script.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question