Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


How can I write this query?

Posted on 2013-11-04
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 111

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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


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 111

Accepted Solution

Ray Paseur earned 1400 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 600 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 111

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 111

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
SMTP = relay-hosting.secureserver.net
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 http://brucegust.com/php_myinfo.php

What do you think?
LVL 111

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 111

Expert Comment

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

604 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