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

x
?
Solved

php issue?

Posted on 2014-03-04
28
Medium Priority
?
303 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
[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
  • 11
  • 11
  • 6
28 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 39904882
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
ID: 39904890
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 1600 total points
ID: 39904901
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

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

 

Author Comment

by:Richard Korts
ID: 39904964
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
ID: 39904976
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
ID: 39905001
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
ID: 39905014
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
ID: 39905016
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 111

Expert Comment

by:Ray Paseur
ID: 39905020
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 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 400 total points
ID: 39905024
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 111

Expert Comment

by:Ray Paseur
ID: 39905030
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
ID: 39905065
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
ID: 39905092
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
ID: 39905151
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
 
LVL 58

Expert Comment

by:Gary
ID: 39905163
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
ID: 39905180
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 111

Expert Comment

by:Ray Paseur
ID: 39905200
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
ID: 39905408
So eloquently put!
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 39906123
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
ID: 39906877
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
ID: 39906904
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
ID: 39906914
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
ID: 39906927
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
ID: 39907399
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
ID: 39907422
Sure will.
The only time you should be changing data is for sanitizing purposes, stripping HTML etc.
0
 
LVL 111

Expert Comment

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

Author Comment

by:Richard Korts
ID: 39910449
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
ID: 39910487
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
In this tutorial viewers will learn how to style elements, such a divs, with a "drop shadow" effect using the CSS box-shadow property Start with a normal styled element, such as a div.: In the element's style, type the box shadow property: "box-shad…
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…
Suggested Courses

610 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