Solved

php issue?

Posted on 2014-03-04
28
281 Views
Last Modified: 2014-03-06
I have a php program; entire source attached.

Note the following:

(1) on lines 7 - 19 is a function whose purpose is obvious.

(2) On line 40 os a MySQL query that utilzes the function.

(3) On line 41 is an echo that shows the query. It shows correctly but all the #&39;, etc., show as ' (I expected it would as the browser converts them).

(4) On line 88, I save the query so I can view source & see what it looks like. It looks like this:

qrysd = "SELECT * from subcat_docs where prim = 'Polywrap' and category = '20&#39 Perforations' and subcategory = 'Black 200/400&#39'";

Notice that the replacements for the single quote are INCORRECT; they are &#39 instead of #&39;

At one point, I had errors in the function elim_quotes where I had the string &#39 (and no ; after). I fixed those things a while back but I though MAYBE that version is still cached. I normally use FireFox, so I exited Fiirefox, restarted it & tried again.

Same thing.

So I thought, I'll look at it in Chrome; I don't think this page has EVER been viewed in Chrome.

Same thing.

So obviously, I'm overlooking something. I'm also attaching the include file bld_lb_text.php.

What am I doing wrong?
product-detailt.php
bld-lb-text.php
0
Comment
Question by:Richard Korts
  • 11
  • 11
  • 6
28 Comments
 
LVL 58

Expert Comment

by:Gary
Comment Utility
And what exactly is the problem?

ps
Using <? for the open tag is bad practise - you should use <?php
0
 

Author Comment

by:Richard Korts
Comment Utility
The problem is that the constructed query does not have the proper HTML character code for a single quote (#&39;) whereas the data in the database DOES; so there is no match (result set has ZERO rows).
0
 
LVL 58

Accepted Solution

by:
Gary earned 400 total points
Comment Utility
Why are you replacing them? You should be just escaping them with a backslash \
But on top of that you are using a deprecated method of SQL, MySQL_query
You need to switch to PDO or MySQLi and this would eliminate the need for escaping

You already have a function there for doing it so I don't understand why you are doing all this work
function esc_quotes($x) {
	$ln = strlen($x);
	$rs = str_replace("'", "\'", $x);
	return $rs;
}

Open in new window


Can you echo out your sql and post it here.
0
 

Author Comment

by:Richard Korts
Comment Utility
I tried escaping the single quote; it does not work. Yesterday, the database table content included the single quotes. It did NOT match by escaping the ' in the query.

I changed the database table to have the #&39; as the code for '.

Here is the echo of the query

subcat docs query = SELECT * from subcat_docs where prim = 'Polywrap' and category = '20' Perforations' and subcategory = 'Black 200/400''
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
You should always store your data as it is, messing around with replacing stuff will always come back to bite you.

Can you do view source and post the SQL echo statement from the HTML, not what you see in the browser

Escaping apostrophes is the standard way, so there is no reason it shouldn't work.
0
 

Author Comment

by:Richard Korts
Comment Utility
Here is the echo in view source:

subcat docs query = SELECT * from subcat_docs where prim = 'Polywrap' and category = '20&#39 Perforations' and subcategory = 'Black 200/400&#39'

FYI, my program has the function

function esc_quotes($x) {
      $ln = strlen($x);
      $rs = str_replace("'", "\'", $x);
      return $rs;
}      

That I used yesterday; DOES NOT work. The single quotes in the database were just that; not escaped (in the database).

Are you saying that in a sql insert statement (in php), a query like "INSERT into table_name (category) VALUES('" . esc_quotes($x) . "')";

will effectively put escaped single quotes into the table that can be then matched by SELECT queries constructed the same way?
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Yes - in the database they would be just apostrophes, you wouldn't see the \ - that is just an escape character that tells php it is part of the value and not the end of the string, it is not inserted into the table.
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Got sidetracked but your function code is wrong, should be

...
       $rs = str_replace("'", "&#39;", $x);
       $rs = str_replace('""', '"', $rs);
       $rs = str_replace('"', '&#34;', $rs);      
...

But stick with the escaped apostrophes - will save you trouble.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Here is how to switch to a supported MySQL extension:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

The function mapping is available on my web site here:
http://www.iconoun.com/mysql_mysqli_pdo_function_map.php

You probably want to learn about escaping in SQL queries.  Best practices include escaping but not munging external input.
http://www.php.net/manual/en/mysqli.real-escape-string.php
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 100 total points
Comment Utility
And you may want to be aware of this.  It should not be an issue for you any more, since the PHP community has known about the problem since 2005.  But some hosts may still have it wrong, or may have back-level PHP installations.  If you read this article and do not fully understand it, please post back with any questions.  I am sure we can explain.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_6630-Magic-Quotes-a-bad-idea-from-day-one.html
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
And you may want to be aware of these PHP functions.  They are there to help you protect your clients from attacks when you get ready to write output to the client browser.  They are not for preparing external data for data base storage, but you must never echo any data to a browser without sanitizing it through these functions.  If you do, you run the risk that you can sabotage your clients, and nobody wants to take responsibility for something like that.  It's another one of those things that we figured out about 10 years ago, but somehow the word seems to get out slowly.
http://php.net/manual/en/function.htmlspecialchars.php
http://php.net/manual/en/function.htmlentities.php
0
 

Author Comment

by:Richard Korts
Comment Utility
OK, thanks.

I will build a SIMPLE test where I will insert some data into the database with escaped single quotes, then try to retrieve it with SELECT with escaped quotes.

Thank you both for your advice regarding MySQLi; I have been aware of that for some time; as best I can see it is a BIG job and I have at least 5 or 6 major operational applications that would require MAJOR overhaul. I'm trying to figure out a way to make the customer pay for at least part of that effort.

I'm sure I am not the only person with this issue
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
It's not that big of an overhaul
Just scare the customers saying all your website will cease to function in a years time unless you pay me to update them (::joking:: but only a bit)
Think Ray prefers MySQLi, I prefer PDO.  Once you have the logic of how it works in your head the transition is fairly easy, or at least I didn't find it too hard to change things around.

At the very least you can just change your connection and how you execute the data, then slowly work through the other bits.

An SQL like this
"SELECT * from subcat_docs where prim = '" . $prd['level1'] . "' and category = '" . elim_quotes($prd['level2']) . "' and subcategory = '" . elim_quotes($prd['level3']) . "'";

wouldn't suddenly break if you change the driver, but for PDO it isn't the way you should do it.
0
 

Author Comment

by:Richard Korts
Comment Utility
Yeah, but I know at least some of my customers will say "Why do I have to pay for it, I didn't ask for it & I don't need it". My site works fine now.

I think I will figure my cost & maybe charge them 1/2, BECAUSE they didn't ask for it. I can justify some of it as you suggest, but more "softly". Some of them have enough tech savvy to recognize this is part of the game.

Richard
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 58

Expert Comment

by:Gary
Comment Utility
It's not so much a case of they can carry on using old versions of PHP and still be ok for time immemorial (don't know if you are hosting these sites yourselves) but most (all) hosts are going to at one point or another say they are no longer supporting the PHP versions that still support the old MySQL query.
But then again you will always have the XP conundrum.

Wasn't it GoDaddy that recently did a mass upgrade of their servers to PHP5 and broke a lot of sites?
0
 

Author Comment

by:Richard Korts
Comment Utility
I have one of my major sites (& a few minor) at GoDaddy. The major one uses the old php; in fact, the program of this question is from that site.

This is all connected with the fact that that this (& other) customers use things like ' for feet and " for inches in product descriptions. Of course there is the standard problem of customer last names like D'Antoni, etc.

Getting back to my current issue, I will try the test I described tomorrow.

R
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Yeah, but I know at least some of my customers will say "Why do I have to pay for it, I didn't ask for it & I don't need it". My site works fine now.
I love sales opportunities like that.  The obvious rejoinder is, "Right, your Chevette may still run, but it is rusting, just like your web site.  Technology is always advancing even if you're not paying attention.  You need your web site to keep working, don't you?  It's much less expensive to make the incremental upgrades than to wait until the day of failure and pay for emergency crisis repairs!  The moment of crisis will be an all-consuming moment of demand for web site remediation, and you don't want to find your web site is down and you're in a bidding war for web development services!  Do the right thing and protect your interests now while the cost is still modest."

FWIW, I am agnostic about MySQLi vs PDO.  Each has its advantages and disadvantages, and some of the decision points are invisible until you've made an inventory of the queries and business logic.  I tend to prefer object-oriented MySQLi because I find that the programming changes are rather muted, whereas in PDO the query strings must change and I feel that introduces risk.  OTOH, if you're working with novice programmers that you do not trust to follow best practices, PDO will help protect you from errors of omission like "forgetting" to escape your external data.  If they "forgot" in MySQL, you can bet they will "forget" in the next extensions.

You can make multiple connections to the data base engine.  You do not have to complete the conversion all at once -- you can do it one query at a time.  The article shows how that can be done.
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
So eloquently put!
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Based on re-reading the question here, I am going to update this article about quotes in PHP.  I will add information about escaping the data when it is used in query strings, and about the use of external data in HTML documents.

Glad you posted the question -- it's a good dialog!
0
 

Author Comment

by:Richard Korts
Comment Utility
To all.

I did the test program. It (seems to) successfully insert the row in the database table, but I cannot retrieve it.

The "test program" is attached.

The attached jpg is the table contents AFTER running the program. I have done it in several variations, each time manually deleting the row in the table BEFORE trying again. Note, the inserted row is the 3rd row.

The echos look like this:

select query = SELECT * from subcat_docs where prim = 'Polywrap' and category = '20\' Perforations' and subcategory = 'Black 200/400\''
Not found

I must be missing something.

Please advise.
test-quotes.php
subcat-docs-table.jpg
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
It's working fine, your code is slightly amiss

$nr = mysql_num_rows;

should be
$nr = mysql_num_rows($res);
0
 

Author Comment

by:Richard Korts
Comment Utility
Aarrrrrrrrrggh!

Obvious, I didn't see it.

Works fine.

I still need to apply it to the "real" program, but it sure looks OK.
0
 

Author Closing Comment

by:Richard Korts
Comment Utility
If I can't make it work in the "real" program, I will post a new question.

SB today.

Thanks to BOTH of you for all your comments & support; you may have opened up a new revenue source for me I had NEVER thought of.

Rkorts
0
 

Author Comment

by:Richard Korts
Comment Utility
To Ray & Cathal,

Just so I am COMPLETELY clear.

If I ALWAYS escape quotes in data that is being inserted and or updated into a MySQL database table AND I ALWAYS escape quotes in a select statement that is looking for things (that may contain escaped quotes in a database table), then it will ALWAYS manage them correctly vis-s-vis MySQL.

Thanks
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Sure will.
The only time you should be changing data is for sanitizing purposes, stripping HTML etc.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Yes.  You should always escape every external data element that is used in any query string, no exceptions.
0
 

Author Comment

by:Richard Korts
Comment Utility
There is a lot of data in several database tables right now that contains #&39 or &#39 (I always get confused about which of # or & comes first).

I wrote a program to fix those. As is my usual practice with "mass" database updates, I first back up the effected tables and , 2nd, I run the program with the query executes commented out, echoing the query instead.

I had the same issue with " (double quotes).

When I look at the echoed queries (see below), there is NEVER a \ before a single quote, but there is one before a double quote.

UPDATE product set level2 = 'Decals/Riser/Valve Markers', level3 = 'Marking Decal', level4 = ' ', headline = 'Potable Water Marking Decal ', prdesc = 'Christy's Potable Water Marking Decal is used to identify above ground potable water installations.' where code = 8548
UPDATE product set level2 = 'Poly Sleeve/Sheeting', level3 = 'Poly Sleeve/Sheeting - Blue', level4 = ' ', headline = '3" X 200' 6 mil Blue Polywrap Sleeve', prdesc = 'For use in identifying and protecting copper service runs. 6 mil average. Not UPC Printed.' where code = 33078
UPDATE product set level2 = 'Bolts & Specialties', level3 = 'Hex Adapters', level4 = ' ', headline = '1 1/2\" X 1 1/2\" Double Male Brass Hex Adapters', prdesc = '' where code = 22212
UPDATE product set level2 = 'Bolts & Specialties', level3 = 'Fire Hoses', level4 = ' ', headline = 'Fire Hoses Rubber Lined 1 1/2\" X 100\'', prdesc = '' where code = 20436
UPDATE product set level2 = 'Bolts & Specialties', level3 = 'Fire Hoses', level4 = ' ', headline = 'Fire Hoses Rubber Lined 1 1/2\" X 50\'', prdesc = '' where code = 20438
UPDATE product set level2 = 'Bolts & Specialties', level3 = 'Breakable Padlocks', level4 = ' ', headline = '3/4\" Breakable Padlock For PIVS', prdesc = '' where code = 23487
UPDATE product set level2 = 'Bolts & Specialties', level3 = 'Breakable Padlocks', level4 = ' ', headline = '5/8\" Breakable Padlock', prdesc = '' where code = 21679
UPDATE product set level2 = 'Hose & Garden ', level3 = '5/8 \" Hoses', level4 = ' ', headline = '5/8 \" x 75' Flexogen Hose', prdesc = '¿" x 75' Flexogen Hose' where code = 1019


Is that to be expected or does it mean I screwed up?

If you feel this should be a separate question, I will be happy to make it so.

Thanks
0
 
LVL 58

Expert Comment

by:Gary
Comment Utility
Is that in PHP or direct.
To update all the &#39; just do a mass query update in phpmyadmin or whatever you are using.

UPDATE table SET column=REPLACE(column,'&#39;','\'')

You could also run it in PHP but for a one off operation its a waste of time writing the code.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Mobile menu navigation , its html and css 6 35
Modify Table Width 6 13
SVG Logo 4 17
addressing a specific html page 9 19
Not sure what the best email signature size is? Are you worried about email signature image size? Follow this best practice guide.
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.
In this tutorial viewers will learn how to embed Flash content in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <object> tag to embed Flash content.: To specify that the object is Flash content, d…
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

763 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

9 Experts available now in Live!

Get 1:1 Help Now