• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

How can I write this query?

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?
  • 6
  • 4
  • 3
2 Solutions

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

Ray PaseurCommented:
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
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.

brucegustPHP DeveloperAuthor Commented:
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?
Ray PaseurCommented:
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!
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.
Ray PaseurCommented:
@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.
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 :)
Ray PaseurCommented:
Yeah, both Magic Quotes and Register Globals were bad ideas from the old days.  They spawned a whole PHP security industry!
brucegustPHP DeveloperAuthor Commented:
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?
Ray PaseurCommented:
Not sure about those semi-colons at the ends of the statements.  You might need to restart PHP, too.
brucegustPHP DeveloperAuthor Commented:
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!
Ray PaseurCommented:
Great!  Thanks for the points and best of luck with it, ~Ray
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now