PHP/MySQL oddity

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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!

Re: PHP/MySQL oddity

On 01-10-2011 20:14, Mason Barge wrote:
Quoted text here. Click to load it

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'.....


Re: PHP/MySQL oddity

Quoted text here. Click to load it

Thank you very much.

Changed it to value=\"$info\"

Re: PHP/MySQL oddity

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

Quoted text here. Click to load it

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:


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

$sql = "UPDATE tablename SET fieldname = '' WHERE



Denis McMahon

Re: PHP/MySQL oddity

Denis McMahon wrote:

Quoted text here. Click to load it

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);

(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

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


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

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

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


In Zend Framework it would be as simple as

  $db = new Zend_Db_Adapter_Pdo_Mysql(…);
      'fieldname' => $fielddata
      'foo' => $condition


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

Re: PHP/MySQL oddity

On 10/1/2011 10:30 PM, Thomas 'PointedEars' Lahn wrote:
Quoted text here. Click to load it

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

Quoted text here. Click to load it

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

exec ('rm -r /');

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

Quoted text here. Click to load it

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

Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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.

Quoted text here. Click to load it

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

Quoted text here. Click to load it
Quoted text here. Click to load it

Adding even more overhead!

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: PHP/MySQL oddity

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

Quoted text here. Click to load it
Quoted text here. Click to load it

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


Arno Welzel

Site Timeline