Solved

MySQL:  escape special characters

Posted on 2014-12-02
12
614 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 500 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

821 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