Form fields to database and back? [message #174534] |
Fri, 17 June 2011 00:36 |
bobmct
Messages: 16 Registered: September 2010
Karma: 0
|
Junior Member |
|
|
I've run into a situation where the data in a particular set of fields
on a form contain lots and lots of special characters (they are regex
notes for a department).
The normal methods I used are failing to retrieve and display the
original characters.
My google searches come up with some rather conflicting
recommendataions. So I thought I would ask this group.
What is the most accurate technique for translating, storing,
retrieving and properly re-displaying such fields with php and mysql?
Thanks
|
|
|
Re: Form fields to database and back? [message #174535 is a reply to message #174534] |
Fri, 17 June 2011 00:43 |
The Natural Philosoph
Messages: 993 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
bobmct wrote:
> I've run into a situation where the data in a particular set of fields
> on a form contain lots and lots of special characters (they are regex
> notes for a department).
>
> The normal methods I used are failing to retrieve and display the
> original characters.
>
> My google searches come up with some rather conflicting
> recommendataions. So I thought I would ask this group.
>
> What is the most accurate technique for translating, storing,
> retrieving and properly re-displaying such fields with php and mysql?
>
> Thanks
>
try BLOBS
that's good for storage and retrieval. But inserting can be an issue if
the regexp contains 'quotes'
|
|
|
Re: Form fields to database and back? [message #174536 is a reply to message #174534] |
Fri, 17 June 2011 01:02 |
Jerry Stuckle
Messages: 2598 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 6/16/2011 8:36 PM, bobmct wrote:
> I've run into a situation where the data in a particular set of fields
> on a form contain lots and lots of special characters (they are regex
> notes for a department).
>
> The normal methods I used are failing to retrieve and display the
> original characters.
>
> My google searches come up with some rather conflicting
> recommendataions. So I thought I would ask this group.
>
> What is the most accurate technique for translating, storing,
> retrieving and properly re-displaying such fields with php and mysql?
>
> Thanks
>
It depends on what the problem is - which is why you're probably finding
conflicting answers. Your question is too vague for a meaningful answer.
First of all, it it ASCII, UTF-8 or some other character set? It does
make a difference, and you want everything (the web page, PHP and MySQL
to agree).
Second of all, how are you storing and retrieving the information? Then
how are you displaying it?
Generally, text information should be stored in the database in text
fields, using the appropriate charset and collation.
But to give you a good answer requires a lot more information.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
Re: Form fields to database and back? [message #174537 is a reply to message #174535] |
Fri, 17 June 2011 01:03 |
Jerry Stuckle
Messages: 2598 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 6/16/2011 8:43 PM, The Natural Philosopher wrote:
> bobmct wrote:
>> I've run into a situation where the data in a particular set of fields
>> on a form contain lots and lots of special characters (they are regex
>> notes for a department).
>>
>> The normal methods I used are failing to retrieve and display the
>> original characters.
>>
>> My google searches come up with some rather conflicting
>> recommendataions. So I thought I would ask this group.
>>
>> What is the most accurate technique for translating, storing,
>> retrieving and properly re-displaying such fields with php and mysql?
>>
>> Thanks
>>
> try BLOBS
> that's good for storage and retrieval. But inserting can be an issue if
> the regexp contains 'quotes'
Blobs should (almost) never be used to store text (why are they called
BINARY LONG OBJECTS?).
And quotes are never a problem with any database if you escape the
strings properly.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
Re: Form fields to database and back? [message #174538 is a reply to message #174536] |
Fri, 17 June 2011 02:34 |
bobmct
Messages: 16 Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On Thu, 16 Jun 2011 21:02:23 -0400, Jerry Stuckle
<jstucklex(at)attglobal(dot)net> wrote:
> It depends on what the problem is - which is why you're probably finding
> conflicting answers. Your question is too vague for a meaningful answer.
>
> First of all, it it ASCII, UTF-8 or some other character set? It does
> make a difference, and you want everything (the web page, PHP and MySQL
> to agree).
>
> Second of all, how are you storing and retrieving the information? Then
> how are you displaying it?
>
> Generally, text information should be stored in the database in text
> fields, using the appropriate charset and collation.
>
> But to give you a good answer requires a lot more information.
Good points. I should have been more clear.
The fields(s) in the Mysql database aredefined as varchar(255)
A typical field the user would enter would be like this:
prd ="^ptmdtr-slb.bna.com^";
I need to store it in the db field then be able to retrieve it and
redisplay it exactly as entered.
Currently I am using:
$fld = htmlspecialchars_decode($fld);
$fld = addslashes($fld);
update table set field_name = '$fld'
To retrieve and redisplay I use:
$fld = $row['field_name'];
$fld = htmlspecialchars($fld);
$fld = stripslashes($fld);
Now I know that I am missing something here so if any ofyou kind
persons would suggest a "usual' sequence of functions to use to
accomplsih this I'd be mighty greatful.
Thanks
|
|
|
Re: Form fields to database and back? [message #174539 is a reply to message #174538] |
Fri, 17 June 2011 03:50 |
Jerry Stuckle
Messages: 2598 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 6/16/2011 10:34 PM, bobmct wrote:
> On Thu, 16 Jun 2011 21:02:23 -0400, Jerry Stuckle
> <jstucklex(at)attglobal(dot)net> wrote:
>
>> It depends on what the problem is - which is why you're probably finding
>> conflicting answers. Your question is too vague for a meaningful answer.
>>
>> First of all, it it ASCII, UTF-8 or some other character set? It does
>> make a difference, and you want everything (the web page, PHP and MySQL
>> to agree).
>>
>> Second of all, how are you storing and retrieving the information? Then
>> how are you displaying it?
>>
>> Generally, text information should be stored in the database in text
>> fields, using the appropriate charset and collation.
>>
>> But to give you a good answer requires a lot more information.
>
> Good points. I should have been more clear.
>
> The fields(s) in the Mysql database aredefined as varchar(255)
>
> A typical field the user would enter would be like this:
>
> prd ="^ptmdtr-slb.bna.com^";
>
> I need to store it in the db field then be able to retrieve it and
> redisplay it exactly as entered.
>
> Currently I am using:
> $fld = htmlspecialchars_decode($fld);
> $fld = addslashes($fld);
>
> update table set field_name = '$fld'
>
> To retrieve and redisplay I use:
> $fld = $row['field_name'];
> $fld = htmlspecialchars($fld);
> $fld = stripslashes($fld);
>
> Now I know that I am missing something here so if any ofyou kind
> persons would suggest a "usual' sequence of functions to use to
> accomplsih this I'd be mighty greatful.
>
> Thanks
>
A varchar field is great, as long as you're using the same charset all
the way through. But there are some other problems in your code:
First of all, you shouldn't be using htmlspecialchars_decode() - you do
not get an encoded string from the browser; it's already been handled.
Second of all, addslashes() is definitely the WRONG function to use -
and has been for years. Before storing in the database, you should use
mysql_real_escape_string($fld).
When you get the data from the database, you should not be using
stripslashes(). There's no need.
Finally, when you go to display the data, you do want to use
htmlspecialchars(), or possibly better for your needs, htmlentities().
See if that doesn't work better.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
Re: Form fields to database and back? [message #174542 is a reply to message #174539] |
Fri, 17 June 2011 11:09 |
bobmct
Messages: 16 Registered: September 2010
Karma: 0
|
Junior Member |
|
|
On Thu, 16 Jun 2011 23:50:12 -0400, Jerry Stuckle
<jstucklex(at)attglobal(dot)net> wrote:
> On 6/16/2011 10:34 PM, bobmct wrote:
>> On Thu, 16 Jun 2011 21:02:23 -0400, Jerry Stuckle
>> <jstucklex(at)attglobal(dot)net> wrote:
>>
>>> It depends on what the problem is - which is why you're probably finding
>>> conflicting answers. Your question is too vague for a meaningful answer.
>>>
>>> First of all, it it ASCII, UTF-8 or some other character set? It does
>>> make a difference, and you want everything (the web page, PHP and MySQL
>>> to agree).
>>>
>>> Second of all, how are you storing and retrieving the information? Then
>>> how are you displaying it?
>>>
>>> Generally, text information should be stored in the database in text
>>> fields, using the appropriate charset and collation.
>>>
>>> But to give you a good answer requires a lot more information.
>>
>> Good points. I should have been more clear.
>>
>> The fields(s) in the Mysql database aredefined as varchar(255)
>>
>> A typical field the user would enter would be like this:
>>
>> prd ="^ptmdtr-slb.bna.com^";
>>
>> I need to store it in the db field then be able to retrieve it and
>> redisplay it exactly as entered.
>>
>> Currently I am using:
>> $fld = htmlspecialchars_decode($fld);
>> $fld = addslashes($fld);
>>
>> update table set field_name = '$fld'
>>
>> To retrieve and redisplay I use:
>> $fld = $row['field_name'];
>> $fld = htmlspecialchars($fld);
>> $fld = stripslashes($fld);
>>
>> Now I know that I am missing something here so if any ofyou kind
>> persons would suggest a "usual' sequence of functions to use to
>> accomplsih this I'd be mighty greatful.
>>
>> Thanks
>>
>
> A varchar field is great, as long as you're using the same charset all
> the way through. But there are some other problems in your code:
>
> First of all, you shouldn't be using htmlspecialchars_decode() - you do
> not get an encoded string from the browser; it's already been handled.
>
> Second of all, addslashes() is definitely the WRONG function to use -
> and has been for years. Before storing in the database, you should use
> mysql_real_escape_string($fld).
>
> When you get the data from the database, you should not be using
> stripslashes(). There's no need.
>
> Finally, when you go to display the data, you do want to use
> htmlspecialchars(), or possibly better for your needs, htmlentities().
>
> See if that doesn't work better.
Thank much Jerry,
That's just the advice I was looking for.
Bob
|
|
|
|
Re: Form fields to database and back? [message #174550 is a reply to message #174542] |
Fri, 17 June 2011 15:18 |
bobmct
Messages: 16 Registered: September 2010
Karma: 0
|
Junior Member |
|
|
Well - another follow-up:
Using your advise this is what the results are:
Data as originally stored in varchar field:
prd ="^ptmdtr-slb.bna.com^";
Data as put back into the varchar field:
prd ="^ptmdtr-slb.bna.com^";
I am using php.5.2.6 (older but I'm locked into it at this point) and
mysql 5.0.25, apache 2.2.11
Upon retrieving the data I use $fld = htmlspecialchars($fld); to display,
and upon storing I use $fld = mysql_real_escape_string($fld); to update.
For what ever reason it appears that the fields are coming back from the
browser (referenced as $fld = $_POST['field_name'];) actually still
encoded.
So you see? I'm at a loss with this (should be minor) issue.
Any more suggestions?
Thanks - Bob
|
|
|
Re: Form fields to database and back? [message #174555 is a reply to message #174538] |
Fri, 17 June 2011 20:03 |
Thomas 'PointedEars'
Messages: 701 Registered: October 2010
Karma: 0
|
Senior Member |
|
|
bobmct wrote:
> A typical field the user would enter would be like this:
>
> prd ="^ptmdtr-slb.bna.com^";
>
> I need to store it in the db field then be able to retrieve it and
> redisplay it exactly as entered.
>
> Currently I am using:
> $fld = htmlspecialchars_decode($fld);
> $fld = addslashes($fld);
>
> update table set field_name = '$fld'
This does not make sense. Either you are writing a CMS, then you should
store all markup verbatim (after removing potentially unwanted elements).
Or you are not, then you should not be receiving markup in the first place.
Unless you write for debugging, you could combine the calls:
$fld = addslashes(htmlspecialchars($fld));
But not even debugging merits two assignments here. (Note that this is just
an example. Do not use addslashes() here.)
If this is just a bad example, then consider this: You should avoid using
such PHP built-ins to escape parts of a database query. addslashes() is
inadequate() to the task. Use extension-provided functions, like
mysql_real_escape_string() or (better) prepared statements (as supported
e.g. by PDO, MySQLi, and sqlsrv, and implemented e.g. by Zend Framework).
> To retrieve and redisplay I use:
> $fld = $row['field_name'];
> $fld = htmlspecialchars($fld);
> $fld = stripslashes($fld);
I do not see why stripslashes() is needed (other than to compensate for the
pointless addslashes() before). htmlspecialchars() is only needed in the
output (so unless you are displaying a value twice, `echo' the return value
of htmlspecialchars() directly – unless your template engine/framework
already does that for you before, which it should be able to if it is any
good), and you should provide suitable additional parameters then (so that,
e.g., Unicode characters can be properly decoded and referred in the
markup).
Of course, you do not need or want to escape all dynamically generated
content, only where it matters (given a proper character encoding,
especially one that is the same in the database and the generated document,
and you making sure that no unwanted HTML is stored/retrieved in the first
place, there is no need to escape the generated content of elements.) Not
trying to escape things that do not need to be escaped can have positive
impact on the performance of a Web application (if you know the value is an
int – as made sure by a properly designed interface –, you don't have to
treat it like a string).
PointedEars
--
Danny Goodman's books are out of date and teach practices that are
positively harmful for cross-browser scripting.
-- Richard Cornford, cljs, <cife6q$253$1$8300dec7(at)news(dot)demon(dot)co(dot)uk> (2004)
|
|
|
Re: Form fields to database and back? [message #174558 is a reply to message #174550] |
Fri, 17 June 2011 20:44 |
Jerry Stuckle
Messages: 2598 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 6/17/2011 11:18 AM, bobm3(at)worthless(dot)info wrote:
> Well - another follow-up:
>
> Using your advise this is what the results are:
>
> Data as originally stored in varchar field:
> prd ="^ptmdtr-slb.bna.com^";
>
> Data as put back into the varchar field:
> prd ="^ptmdtr-slb.bna.com^";
>
> I am using php.5.2.6 (older but I'm locked into it at this point) and
> mysql 5.0.25, apache 2.2.11
>
> Upon retrieving the data I use $fld = htmlspecialchars($fld); to display,
>
> and upon storing I use $fld = mysql_real_escape_string($fld); to update.
>
> For what ever reason it appears that the fields are coming back from the
> browser (referenced as $fld = $_POST['field_name'];) actually still
> encoded.
>
> So you see? I'm at a loss with this (should be minor) issue.
>
> Any more suggestions?
>
> Thanks - Bob
How are you displaying the data in the browser? What do you see on the
screen and in the page source?
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
Re: Form fields to database and back? [message #174563 is a reply to message #174555] |
Fri, 17 June 2011 23:52 |
bobmct
Messages: 16 Registered: September 2010
Karma: 0
|
Junior Member |
|
|
All good points everyone, of course. But with extensive testing today
here's what I had to end up with for consistent results:
From field to database I used mysql_real_escape_string.
When I look at the actual data stored in the db field that function
inserted backslashes before each double quote.
To display the retrieved db field I ran it through htmlspecialchars()
but the backslashes still remained. I had to use stripslashes to
remove them.
And no, this is NOT a cms. Its a stand alone database update program.
Works for now.
And a general comment on nesting functions vs individual lines...
I've been coding for many decades and quite often, including prior to
this project, I have had to trudge through code written by others.
When one has no idea about the code and no documentation let alone
self documented code, nested functions are difficult to decode.
Of course it can be done but I've learned that when programs are
running on 16 core 48GB RAM systems, it makes little difference in
performance but a whole LOT of difference for the next person to
understand.
Just my $.02 worth.
|
|
|
Re: Form fields to database and back? [message #174567 is a reply to message #174563] |
Sat, 18 June 2011 01:01 |
Jerry Stuckle
Messages: 2598 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 6/17/2011 7:52 PM, bobmct wrote:
> All good points everyone, of course. But with extensive testing today
> here's what I had to end up with for consistent results:
>
> From field to database I used mysql_real_escape_string.
>
> When I look at the actual data stored in the db field that function
> inserted backslashes before each double quote.
>
Then you have done something else, like used addslashes() somewhere.
Alternatively, magic_quotes_gpc may be set on your server (it should NOT
be; it has been deprecated for years and will be removed in PHP 6). But
mysql_real_escape_string() will not cause backslashes to be added to the
data in the database; when you retrieve the data it will be exactly as
it originally was.
> To display the retrieved db field I ran it through htmlspecialchars()
> but the backslashes still remained. I had to use stripslashes to
> remove them.
>
That's because you did something else beforehand which is invalid.
> And no, this is NOT a cms. Its a stando alone database update program.
>
> Works for now.
>
> And a general comment on nesting functions vs individual lines...
>
> I've been coding for many decades and quite often, including prior to
> this project, I have had to trudge through code written by others.
> When one has no idea about the code and no documentation let alone
> self documented code, nested functions are difficult to decode.
>
> Of course it can be done but I've learned that when programs are
> running on 16 core 48GB RAM systems, it makes little difference in
> performance but a whole LOT of difference for the next person to
> understand.
>
> Just my $.02 worth.
PHP does not allow nested functions. I'm not sure where that came up
(you didn't quote the relevant text).
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|
Re: Form fields to database and back? [message #174571 is a reply to message #174563] |
Sat, 18 June 2011 06:35 |
Thomas 'PointedEars'
Messages: 701 Registered: October 2010
Karma: 0
|
Senior Member |
|
|
bobmct wrote:
> From field to database I used mysql_real_escape_string.
>
> When I look at the actual data stored in the db field that function
> inserted backslashes before each double quote.
>
> To display the retrieved db field I ran it through htmlspecialchars()
> but the backslashes still remained. I had to use stripslashes to
> remove them.
Then you are doing something wrong. mysql_real_escape_string() – AISB,
prepared statements (PS) with MySQLi or PDO are preferable to that – only
escapes the data for the query, so that SQL code injection is prevented.
It does _not_ change the data to be stored. So when you retrieve the data
you should not need to unescape anything. Perhaps you have used
mysql_real_escape_string() on the retrieved data also, but that is _not_ its
purpose.
> Works for now.
By chance. mysql_real_escape_string() does more than addslashes(), which is
why it is preferable to that. (And PS are preferable to it because they
consider the type automatically, among other advantages.)
PointedEars
--
Use any version of Microsoft Frontpage to create your site.
(This won't prevent people from viewing your source, but no one
will want to steal it.)
-- from <http://www.vortex-webdesign.com/help/hidesource.htm> (404-comp.)
|
|
|
Re: Form fields to database and back? [message #174620 is a reply to message #174567] |
Wed, 22 June 2011 16:05 |
Captain Paralytic
Messages: 204 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On Jun 18, 2:01 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> On 6/17/2011 7:52 PM, bobmct wrote:
> PHP does not allow nested functions. I'm not sure where that came up
> (you didn't quote the relevant text).
It came from TPL's suggestion of:
$fld = addslashes(htmlspecialchars($fld));
and are you sure that php doesn't allow this?
|
|
|
Re: Form fields to database and back? [message #174623 is a reply to message #174620] |
Wed, 22 June 2011 17:15 |
Jerry Stuckle
Messages: 2598 Registered: September 2010
Karma: 0
|
Senior Member |
|
|
On 6/22/2011 12:05 PM, Captain Paralytic wrote:
> On Jun 18, 2:01 am, Jerry Stuckle<jstuck...@attglobal.net> wrote:
>> On 6/17/2011 7:52 PM, bobmct wrote:
>> PHP does not allow nested functions. I'm not sure where that came up
>> (you didn't quote the relevant text).
> It came from TPL's suggestion of:
> $fld = addslashes(htmlspecialchars($fld));
> and are you sure that php doesn't allow this?
Ah, that's what he's talking about.
Sure, that's allowed. But that's not really "nesting". Nesting
functions would be more like:
function foo() {
...
function bar() {
...
}
}
Similar to nested loops.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex(at)attglobal(dot)net
==================
|
|
|