Solved

MySQL:  escape special characters

Posted on 2014-12-02
12
567 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
 
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

943 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

Need Help in Real-Time?

Connect with top rated Experts

3 Experts available now in Live!

Get 1:1 Help Now