Solved

How can I write this query?

Posted on 2013-11-04
13
221 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
  • 6
  • 4
  • 3
13 Comments
 
LVL 17

Expert Comment

by:bigeven2002
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
 

Author Comment

by:brucegust
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 350 total points
Comment Utility
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
Comment Utility
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
Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
@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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

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

Author Comment

by:brucegust
Comment Utility
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 108

Expert Comment

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
get radio button vale in array 7 35
Php Array Diff 3 26
Jquery Autocomplete PHP script 3 20
mysql left join sentence 7 19
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
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.

771 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

10 Experts available now in Live!

Get 1:1 Help Now