Solved

MySQL:  escape special characters

Posted on 2014-12-02
12
551 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The viewer will learn how to dynamically set the form action using jQuery.
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…

706 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

19 Experts available now in Live!

Get 1:1 Help Now