Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #3253 > unrolled thread

PHP/MySQL oddity

Back to article view | Back to comp.lang.php

Contents

  PHP/MySQL oddity Mason Barge <masonbarge@gmail.com> - 2011-10-01 14:14 -0400
    Re: PHP/MySQL oddity Luuk <Luuk@invalid.lan> - 2011-10-01 20:31 +0200
      Re: PHP/MySQL oddity Mason Barge <masonbarge@gmail.com> - 2011-10-01 17:31 -0400
        Re: PHP/MySQL oddity Denis McMahon <denismfmcmahon@gmail.com> - 2011-10-01 22:23 +0000
          Re: PHP/MySQL oddity Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2011-10-02 04:30 +0200
            Re: PHP/MySQL oddity Jerry Stuckle <jstucklex@attglobal.net> - 2011-10-02 09:55 -0400
    Re: PHP/MySQL oddity Arno Welzel <usenet@arnowelzel.de> - 2011-10-02 19:35 +0200

#3253 — PHP/MySQL oddity

I've been building a form that takes default text input from a database
(PHP 5.2, MySQL 5).  It's to allow a user to modify previously entered
text without rewriting the entire thing.

The database comes from user input and it handles uploading apostrophes to
the database and then downloading them into plain HTML fine.  But when the
same downloaded variable is put into a text input in a form, the
apostrophe and everything after it is truncated.

Running the variable through either addslashes() or
mysql_real_escape_string() adds a backslash, but not the apostrophe!

If that's not clear, I do a SELECT query and take, say, the "info" column,
set its value to $info, and write:

<form name=...><input type=text . . . value = '<?php echo $info;?>'

Or echo the whole thing, or double quotes around the variable.  I just
can't figure out how to make the apostrophe appear to save my life.

This only happens in <input type=text> where the field is VARCHAR.  I also
have textarea input (i.e. <textarea . . .>$info</textarea>)  that download
their value from a TEXT column but they function fine!

[toc] | [next] | [standalone]

#3254

On 01-10-2011 20:14, Mason Barge wrote:
> <form name=...><input type=text . . . value = '<?php echo $info;?>'

After PHP is done, and when $info contains "some'text" (without the 
double-quotes) What would you guess to be send to your browser?

Indeed, it's:
<form name=...><input type=text . . . value = 'some'text'.....
                                                     ^^^^


-- 
Luuk

[toc] | [prev] | [next] | [standalone]

#3255

On Sat, 01 Oct 2011 20:31:31 +0200, Luuk <Luuk@invalid.lan> wrote:

>On 01-10-2011 20:14, Mason Barge wrote:
>> <form name=...><input type=text . . . value = '<?php echo $info;?>'
>
>After PHP is done, and when $info contains "some'text" (without the 
>double-quotes) What would you guess to be send to your browser?
>
>Indeed, it's:
><form name=...><input type=text . . . value = 'some'text'.....
>                                                     ^^^^

Thank you very much.

Changed it to value=\"$info\"

[toc] | [prev] | [next] | [standalone]

#3256

On Sat, 01 Oct 2011 17:31:38 -0400, Mason Barge wrote:

> On Sat, 01 Oct 2011 20:31:31 +0200, Luuk <Luuk@invalid.lan> wrote:
> 
>>On 01-10-2011 20:14, Mason Barge wrote:
>>> <form name=...><input type=text . . . value = '<?php echo $info;?>'
>>
>>After PHP is done, and when $info contains "some'text" (without the
>>double-quotes) What would you guess to be send to your browser?
>>
>>Indeed, it's:
>><form name=...><input type=text . . . value = 'some'text'.....

> Thank you very much.

> Changed it to value=\"$info\"

You need to understand where and when to use the escape functions.

Specifically, when using text that was supplied by the user in any sql 
statement, use e.g:

<?php

if (isset($_POST['fieldname'])) 
  {
  $fielddata = mysql_real_escape_string($_POST['fieldname']);
  }
else 
  {
  $fielddata = "some_default_value";
  }

$sql = "UPDATE tablename SET fieldname = '{$fielddata}' WHERE 
_condition_";

?>

Rgds

Denis McMahon

[toc] | [prev] | [next] | [standalone]

#3257

Denis McMahon wrote:

> On Sat, 01 Oct 2011 17:31:38 -0400, Mason Barge wrote:
>> On Sat, 01 Oct 2011 20:31:31 +0200, Luuk <Luuk@invalid.lan> wrote:
>>> On 01-10-2011 20:14, Mason Barge wrote:
>>>> <form name=...><input type=text . . . value = '<?php echo $info;?>'
>>> After PHP is done, and when $info contains "some'text" (without the
>>> double-quotes) What would you guess to be send to your browser?
>>>
>>> Indeed, it's:
>>> <form name=...><input type=text . . . value = 'some'text'.....
>> 
>> Thank you very much.
>> 
>> Changed it to value=\"$info\"
> 
> You need to understand where and when to use the escape functions.
> 
> Specifically, when using text that was supplied by the user in any sql
> statement, use e.g:
> 
> <?php
> 
> if (isset($_POST['fieldname']))
>   {
>   $fielddata = mysql_real_escape_string($_POST['fieldname']);
>   }
> else
>   {
>   $fielddata = "some_default_value";
>   }
> 
> $sql = "UPDATE tablename SET fieldname = '{$fielddata}' WHERE
> _condition_";

This approach fails for

  $fielddata = "That's my default";

And let us hope that the default value is not, e. g. because of an 
accidental copy & paste operation, 

  $fielddata = "That'; DROP TABLE tablename; …";

It is a good idea to always escape values in database queries.

It is an even better idea to use prepared statements to do that only when 
necessary and in the way required by the used DBMS.  MySQLi and PDO are 
among the DBA PHP modules which support this.

  $mysqli = new Mysqli(…);
  $stmt = $mysqli->prepare(
    "UPDATE tablename SET fieldname=? WHERE foo = ?");
  $stmt->bind_param('ss', $fielddata, $condition);
  $stmt->execute();
  $stmt->close();

(If you do not like the object-oriented variant you can still use the simple 
functional variant.  Function identifiers would be `mysqli_prepare' and so 
forth.)

Using the mysqli module, and the corresponding MySQL versions, instead of 
mysql, and corresponding MySQL versions, is /*strongly*/ recommended:

<http://php.net/manual/en/mysqli.overview.php>

However, I prefer PDO as it is more flexible in terms of configuration and 
reuse:

  $pdo = new PDO(…);
  $stmt = $pdo->prepare(
    "UPDATE tablename SET fieldname=:fieldname WHERE foo = :condition");
  $stmt->execute(array(
    'fieldname' => $fielddata,
    'condition' => $condition
  ));
  $stmt->closeCursor();

(You can also still bind your parameters the mysqli way.)

<http://php.net/manual/en/book.pdo.php>

In Zend Framework it would be as simple as

  $db = new Zend_Db_Adapter_Pdo_Mysql(…);
  $db->update('tablename',
    array(
      'fieldname' => $fielddata
    ),
    array(
      'foo' => $condition
    ));

<http://framework.zend.com/apidoc/1.11/db_Db_Adapter_Pdo_Mysql.html#%5CZend_Db_Adapter_Pdo_Mysql>


PointedEars
-- 
    realism:    HTML 4.01 Strict
    evangelism: XHTML 1.0 Strict
    madness:    XHTML 1.1 as application/xhtml+xml
                                                    -- Bjoern Hoehrmann

[toc] | [prev] | [next] | [standalone]

#3259

On 10/1/2011 10:30 PM, Thomas 'PointedEars' Lahn wrote:
> Denis McMahon wrote:
>
>> On Sat, 01 Oct 2011 17:31:38 -0400, Mason Barge wrote:
>>> On Sat, 01 Oct 2011 20:31:31 +0200, Luuk<Luuk@invalid.lan>  wrote:
>>>> On 01-10-2011 20:14, Mason Barge wrote:
>>>>> <form name=...><input type=text . . . value = '<?php echo $info;?>'
>>>> After PHP is done, and when $info contains "some'text" (without the
>>>> double-quotes) What would you guess to be send to your browser?
>>>>
>>>> Indeed, it's:
>>>> <form name=...><input type=text . . . value = 'some'text'.....
>>>
>>> Thank you very much.
>>>
>>> Changed it to value=\"$info\"
>>
>> You need to understand where and when to use the escape functions.
>>
>> Specifically, when using text that was supplied by the user in any sql
>> statement, use e.g:
>>
>> <?php
>>
>> if (isset($_POST['fieldname']))
>>    {
>>    $fielddata = mysql_real_escape_string($_POST['fieldname']);
>>    }
>> else
>>    {
>>    $fielddata = "some_default_value";
>>    }
>>
>> $sql = "UPDATE tablename SET fieldname = '{$fielddata}' WHERE
>> _condition_";
>
> This approach fails for
>
>    $fielddata = "That's my default";
>

No it will not.  Denis's method is the correct way to escape the string.

> And let us hope that the default value is not, e. g. because of an
> accidental copy&  paste operation,
>
>    $fielddata = "That'; DROP TABLE tablename; …";
>

Gee, let's hope you know how to program.  There are a lot of things 
which can go wrong when you write the wrong code.  For instance, let's 
hope that you don't code, e.g. because of an accidental copy & paste 
operation:

exec ('rm -r /');

And BTW - your code will fail anyway.  mysql_query() will not execute 
multiple queries in a single call.

> It is a good idea to always escape values in database queries.
>

Not necessary when you have a known, fixed default value.

> It is an even better idea to use prepared statements to do that only when
> necessary and in the way required by the used DBMS.  MySQLi and PDO are
> among the DBA PHP modules which support this.
>
>    $mysqli = new Mysqli(…);
>    $stmt = $mysqli->prepare(
>      "UPDATE tablename SET fieldname=? WHERE foo = ?");
>    $stmt->bind_param('ss', $fielddata, $condition);
>    $stmt->execute();
>    $stmt->close();
>
> (If you do not like the object-oriented variant you can still use the simple
> functional variant.  Function identifiers would be `mysqli_prepare' and so
> forth.)
>

A matter of opinion.  For instance, prepared statements also have 
additional overhead.  Neither is "better" than the other - as long as 
you escape your data.

> Using the mysqli module, and the corresponding MySQL versions, instead of
> mysql, and corresponding MySQL versions, is /*strongly*/ recommended:
>
> <http://php.net/manual/en/mysqli.overview.php>
>

The mysqli interface does have some advantages - but also some 
disadvantages (i.e. more processing required).  Neither is "best" and 
both should be evaluated in the context they will be used.

> However, I prefer PDO as it is more flexible in terms of configuration and
> reuse:
>
>    $pdo = new PDO(…);
>    $stmt = $pdo->prepare(
>      "UPDATE tablename SET fieldname=:fieldname WHERE foo = :condition");
>    $stmt->execute(array(
>      'fieldname' =>  $fielddata,
>      'condition' =>  $condition
>    ));
>    $stmt->closeCursor();
>
> (You can also still bind your parameters the mysqli way.)
>
> <http://php.net/manual/en/book.pdo.php>
>

Which has significant overhead when compared to the mysql and mysqli 
interfaces.  And any code can be reused if it is designed properly.

> In Zend Framework it would be as simple as
>
>    $db = new Zend_Db_Adapter_Pdo_Mysql(…);
>    $db->update('tablename',
>      array(
>        'fieldname' =>  $fielddata
>      ),
>      array(
>        'foo' =>  $condition
>      ));
>
> <http://framework.zend.com/apidoc/1.11/db_Db_Adapter_Pdo_Mysql.html#%5CZend_Db_Adapter_Pdo_Mysql>
>
>
> PointedEars

Adding even more overhead!

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#3268

Mason Barge, 2011-10-01 20:14:

> I've been building a form that takes default text input from a database
> (PHP 5.2, MySQL 5).  It's to allow a user to modify previously entered
> text without rewriting the entire thing.
[...]
> <form name=...><input type=text . . . value = '<?php echo $info;?>'
> 
> Or echo the whole thing, or double quotes around the variable.  I just
> can't figure out how to make the apostrophe appear to save my life.

<?php echo htmlspecialchars($info); ?>

HTH


-- 
Arno Welzel
http://arnowelzel.de
http://de-rec-fahrrad.de

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.php

csiph-web