How can I write this query?

Posted on 2013-11-04
Last Modified: 2013-11-06
Here's my select statement: SELECT * FROM `class_content` WHERE class_name='It\'s All Good'

I have a record that matches this, but the "It\'s All Good" is messing things up.

I've tried to escape the string, but I can't get it to work and the "It\'s All Good" looks just that way in the database.

What am I doing wrong?
Question by:brucegust
  • 6
  • 4
  • 3
LVL 17

Expert Comment

ID: 39623296

Your string should not look like that in the database itself.  It should read "It's All Good".  Right now, it thinks that backslash is part of the data.

You could try a double escape string which may or may not work (may cause syntax error), but I would change that entry in the database to remove the backslash, then try your query.
# With double escape
SELECT * FROM `class_content` WHERE class_name = 'It\\'s All Good'
# or triple escape for the single quote
SELECT * FROM `class_content` WHERE class_name = 'It\\\'s All Good'

Open in new window

If it got that way in the database with an INSERT query, then the syntax is using too many addslashes, either from the PHP script or the magic quotes from php.ini or a combination of both.
LVL 17

Expert Comment

ID: 39623368
After doing some testing, it seems to work with 5 backslashes:
$query = 'SELECT * FROM `class_content` WHERE class_name = "It\\\\\'s All Good"';
# or
$query = "SELECT * FROM `class_content` WHERE class_name = 'It\\\\\'s All Good'";

Open in new window

LVL 109

Expert Comment

by:Ray Paseur
ID: 39623985
Probably the first thing to do is fix the data base.  The presence of escape characters (the backslash) indicates that there was a logic or configuration error at the time this row was written.  The article on Magic Quotes may explain how this can happen.

A way to fix would be something like this:

1. SELECT the key and the munged column
2. Use PHP stripslashes() a few times on the column
3. Use the correct escape function or parameter binding, depending on what MySQL extension you're using
4. UPDATE the row to replace the munged column with the valid data.

Going forward, you would want to disable Magic Quotes if it is not already disabled.  When you do that you will have to inspect every data field that gets used in every query to be sure the correct escape or binding is being used.

Magic Quotes is deprecated at PHP 5.3 and removed at PHP 5.4.

If (and I think this is a very small if) you find that you already have Magic Quotes turned off, and yet the data base is getting polluted with escape characters, then you have a logic error in the programming leading to double-escape sequences.

HTH, ~Ray
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.


Author Comment

ID: 39624200
Here are my phpmyinfo settings as far as Magic Quotes:

magic_quotes_gpc              On      On
magic_quotes_runtime      Off      Off
magic_quotes_sybase      Off      Off

Here's what I use when I'm getting ready to insert something into my database:

$element_name= mysqli_real_escape_string($cxn, trim($_POST['element_name']));

Based on what you're seeing, what do I need to change when I'm getting ready to insert stuff into the database in order to avoid the overage of backslashes?
LVL 109

Accepted Solution

Ray Paseur earned 350 total points
ID: 39624360
Please read the article about Magic Quotes.  It addresses EXACTLY what is happening to you and what you should do about it.

Hint: You're not alone here.  This problem is so common that it was worth writing the article to save time explaining it over and over to each developer who ran into the same issue!
LVL 17

Assisted Solution

bigeven2002 earned 150 total points
ID: 39624449
To append to what has already been said, your variable with the MySQL real escape is correct, in your php.ini, the magic_quotes_gpc needs to be set to off.  Gpc stands for get, post, cookie, referring to the superglobals, in this case you are using the post superglobal.  If you do not have access to the php.ini, (Ray correct me if I'm wrong) you can create your own php.ini file in the parent directory of your site, or you can use ini_set to turn off magic_quotes_gpc for that script only in your php file itself.
LVL 109

Expert Comment

by:Ray Paseur
ID: 39624490
@bigeven2002: I think you're mostly on firm ground there.  I have not seen the code base and if I have to make a blind recommendation it would be to use ini_set(), EXCEPT YOU CAN'T DO THAT!  The data has already been munged before the script starts.

That's why this is a painstaking process - you either have Magic Quotes or you do not.  The one script-selective thing you can do is pointed out in the article in the no_magic_quotes() function.  If you follow that process, you can effectively turn off magic quotes on a script-by-script basis.
LVL 17

Expert Comment

ID: 39626320
Good to know.  I had used ini_set a couple times back in the PHP4 days but haven't since.  I never thought to create a global no_magic_quotes function, learned something new :)
LVL 109

Expert Comment

by:Ray Paseur
ID: 39626767
Yeah, both Magic Quotes and Register Globals were bad ideas from the old days.  They spawned a whole PHP security industry!

Author Comment

ID: 39627296
Ray, I read your article and I'm planning on using that no_magic_quotes function, but I wanted to try and turn them off using the php.ini file I've got in my root directory.

Seems pretty intuitive. Here's my php.ini file:

register_globals = off
allow_url_fopen = off
magic_quotes_gpc = Off;
magic_quotes_runtime = Off;
magic_quotes_sybase = Off;

expose_php = Off
max_input_time = 60
variables_order = "EGPCS"
extension_dir = ./
upload_tmp_dir = /tmp
precision = 12
url_rewriter.tags = "a=href,area=href,frame=src,input=src,form=,fieldset="

; Only uncomment zend optimizer lines if your application requires Zend Optimizer support


; -- Be very careful to not to disable a function which might be needed!
; -- Uncomment the following lines to increase the security of your PHP site.

;disable_functions = "highlight_file,ini_alter,ini_restore,openlog,passthru,
;		      phpinfo, exec, system, dl, fsockopen, set_time_limit,
;                     popen, proc_open, proc_nice,shell_exec,show_source,symlink"

Open in new window

I installed a php_myinfo.php on my site and here's where I'm puzzled. While I've got Magic Quotes turned off according to php.ini file, it's still on according to my php_myinfo file.

What am I missing?

My php_myinfo file is at

What do you think?
LVL 109

Expert Comment

by:Ray Paseur
ID: 39627310
Not sure about those semi-colons at the ends of the statements.  You might need to restart PHP, too.

Author Comment

ID: 39627447
Alright, Ray! I just spoke with my ISP and after renaming my php.ini to php5.ini, they assured me my changes will go into effect within 24 hours. In the interim, I'll be employing your counsel as far as shutting off Magic Quotes via a function.

Thanks so much!
LVL 109

Expert Comment

by:Ray Paseur
ID: 39627466
Great!  Thanks for the points and best of luck with it, ~Ray

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
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…

831 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