Solved

How can I write this query?

Posted on 2013-11-04
13
230 Views
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?
0
Comment
Question by:brucegust
[X]
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
13 Comments
 
LVL 17

Expert Comment

by:bigeven2002
ID: 39623296
Hello,

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.
0
 
LVL 17

Expert Comment

by:bigeven2002
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

0
 
LVL 110

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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:brucegust
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?
0
 
LVL 110

Accepted Solution

by:
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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_6630-Magic-Quotes-a-bad-idea-from-day-one.html

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!
0
 
LVL 17

Assisted Solution

by:bigeven2002
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.
0
 
LVL 110

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.
0
 
LVL 17

Expert Comment

by:bigeven2002
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 :)
0
 
LVL 110

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!
0
 

Author Comment

by:brucegust
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

;[Zend]
;zend_optimizer.optimization_level=15
;zend_extension_manager.optimizer=/usr/local/Zend/lib/Optimizer-3.3.3
;zend_extension_manager.optimizer_ts=/usr/local/Zend/lib/Optimizer_TS-3.3.3
;zend_extension=/usr/local/Zend/lib/Optimizer-3.3.3/ZendExtensionManager.so
;zend_extension_ts=/usr/local/Zend/lib/Optimizer_TS-3.3.3/ZendExtensionManager_TS.so


; -- 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?
0
 
LVL 110

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

Author Comment

by:brucegust
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!
0
 
LVL 110

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

749 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