[SOLVED] Apostrophe Trouble - updating database

[EternalSorrow]

This is perhaps an easy puzzle to solve, but I'm stumped.

 

I have a form which updates a database and am having difficulty adapting to apostrophes.  I've figured out some code, through the use of the escape(), to get around the error message and insert into the database, but editing is causing problems.

 

Whenever I try to edit the content it gives me the 'success' message, but when I return to the information page the content has not been edited.  I've searched the internet and this site for answers, but none seem to apply directly to my situation.

 

Any hints, suggestions, jokes, or comments on my situation?

 

The editable field is here called 'summary,' and here's the code for the update:

<?
if ($_POST["submit"])
{
$title = $_POST["title"];
$author = $_POST["author"];
$summary = $_POST["summary"];
$datetime = $_POST['date("d/m/y H:i:s")'];

$sql = sprintf("UPDATE information SET author='%s',title='%s',summary='%s',datetime=NOW() WHERE `author`= '%s' AND title = '%s' ", mysql_real_escape_string($author), mysql_real_escape_string($title), mysql_real_escape_string($author), mysql_real_escape_string($title), mysql_real_escape_string($summary)) or die(mysql_error());

$result = mysql_query($sql) or die(mysql_error());
echo "The information has been updated.  <a href=\"infoedit.php?cmd=edit&author=$author&title=$title\">Care to continuing editing</a> or <a href=\"info.php?author=$author&title=$title\">view the edited content</a>?";
}
}
?>

 

And as a reference, here's the input form:

<?
if($_GET["cmd"]=="edit" || $_POST["cmd"]=="edit")
{
if (!isset($_POST["submit"]))
{
$title = $_GET["title"];
$author = $_GET["author"];
$sql = sprintf("SELECT * FROM information WHERE `author`= '%s' AND title = '%s'", mysql_real_escape_string($author), mysql_real_escape_string($title));
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>

<form action="infoedit.php" method="post">
<input type=hidden name="author" value="<?php echo $myrow["author"] ?>">
<input type=hidden name="title" value="<?php echo $myrow["title"] ?>">
<table align="left">

<tr><td>Title:<td> <?php echo $myrow["title"] ?></td></tr>

<tr><td>Author:<td> <?php echo $myrow["author"] ?></td></tr>

<tr><td>Summary:</td><td><textarea name="summary"><? echo stripslashes($myrow["summary"]); ?></textarea></td></tr>

<tr><td><td><input type="hidden" name="cmd" value="edit">
<input type="reset" name="Reset">
<br><input type="submit" name="submit" value="Submit"></td></tr>

</table>
</form>

<? } ?>

[phpSensei]

what do you mean adapting to apostrophee's?

 

can you elaborate a little more please

[roopurt18]

<?php
$sql = sprintf("
    UPDATE information SET 
        author='%s', // a
        title='%s', // b
        summary='%s', // c
        datetime=NOW() 
    WHERE 
        `author`= '%s'  // d
        AND title = '%s' ",  // e
    mysql_real_escape_string($author), // a
    mysql_real_escape_string($title), // b
    mysql_real_escape_string($author), // c
    mysql_real_escape_string($title), // d
    mysql_real_escape_string($summary) // e
);
?>

 

Your arguments to sprintf aren't even in the right order.

 

You've got another serious flaw in your logic there as well that deals with setting author and title while using the same values to retrieve the record in your where clause.

[EternalSorrow]

what do you mean adapting to apostrophee's?

 

can you elaborate a little more please

 

When someone updates the current row and the $summary holds an apostrophe, an error message appears like this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's arms. How will their love survive, when the ties that bind begins to slowly un' at line 1

 

So in order to get around this, I use the sprintf and change the summary from this:

 

summary='$summary'

 

to this:

 

summary='%s'

 

and use the mysql_real_escape_string() to finalize the change.  Unfortunately by changing this code the query will no longer update the database, and I'm not sure why.  It does work prior to the change.

 

Your arguments to sprintf aren't even in the right order.

 

If it ain't broke, don't fix it.

 

You've got another serious flaw in your logic there as well that deals with setting author and title while using the same values to retrieve the record in your where clause.

 

Not serious enough to throw an error message, but I agree the duplicate 'title' and 'author' fields are redundant, so I removed them.

 

Here's what the updated code for the 'update' query looks like now (the form is unchanged):

<?
if ($_POST["submit"])
{
$author = $_POST["author"];
$title = $_POST["title"];
$summary = $_POST["summary"];
$datetime = $_POST['date("d/m/y H:i:s")'];

$sql = sprintf("UPDATE information SET summary='%s',datetime=NOW() WHERE `author`= '%s' AND title = '%s' ", mysql_real_escape_string($author), mysql_real_escape_string($title), mysql_real_escape_string($summary)) or die(mysql_error());

$result = mysql_query($sql) or die(mysql_error());
echo "The information has been updated.  <a href=\"infoedit.php?cmd=edit&author=$author&title=$title\">Care to continuing editing</a> or <a href=\"info.php?author=$author&title=$title\">view the edited content</a>?";
}
}
?>

[EternalSorrow]

Solved.  Rather than placing the mysql_real_escape_string() within the query, I placed the string within the post of the 'summary' field, like so:

 

$summary = mysql_real_escape_string ($_POST["summary"]);

 

So the final code looks like this:

<?
if ($_POST["submit"])
{
$author = $_POST["author"];
$title = $_POST["title"];
$summary = mysql_real_escape_string ($_POST["summary"]);
$datetime = $_POST['date("d/m/y H:i:s")'];

$sql = sprintf("UPDATE information SET `summary`='$summary',datetime=NOW() WHERE `author`= '%s' AND title = '%s' ", mysql_real_escape_string($author), mysql_real_escape_string($title)) or die(mysql_error());

$result = mysql_query($sql) or die(mysql_error());
echo "The information has been updated.  <a href=\"infoedit.php?cmd=edit&author=$author&title=$title\">Care to continuing editing</a> or <a href=\"info.php?author=$author&title=$title\">view the edited content</a>?";
}
}
?>

[roopurt18]

You could have easily solved this yourself by now if you'd done a couple of things.

 

I told you your arguments to sprintf() aren't in the right order.  The best thing to do would have been to wander over to the PHP manual and read the documentation on sprintf().  By reading the documentation for the function you are calling, you determine whether or not you are using it correctly.

 

Instead you opted to blindly remove or add things from your call to sprintf() and guess what?  Your arguments to sprintf still aren't even in the right order.

 

Also, when programs don't work you have to debug them because they have a problem.  You need information to solve problems.  You could easily alter your code to give you more information about what is causing the problem.  Such as:

$result = mysql_query($sql) or die($sql . ' resulted in error: ' . mysql_error());

Now when it dies, you'll get not only the MySQL error but the SQL statement that caused the error.

 

Now if you actually take the time to read the SQL statement, you'll realize it's totally wrong.

 

At that point you ask yourself, "Why is my statement wrong?  Well I'm setting it with sprintf() so I better check my code against the documentation and make sure I'm using it correctly."

 

Now don't take this the wrong way.  I'm not being mean.  I'm trying to teach you how to resolve your own issues.

 

Lastly, you can remove the or die() on the sprintf() line; it's completely useless.

 

[Psycho]

Solved.  Rather than placing the mysql_real_escape_string() within the query, I placed the string within the post of the 'summary' field, like so:

 

That was NOT the problem. The problem was that the variables were out of order as roopurt18 stated previously and your comeback was

[qiuote]If it ain't broke, don't fix it.

 

Write clean, organized code to prevent these types of smacktard mistakes

 

if ($_POST["submit"])
{
    $author   = mysql_real_escape_string($_POST["author"]);
    $title    = mysql_real_escape_string($_POST["title"]);
    $summary  = mysql_real_escape_string($_POST["summary"]);
    $datetime = mysql_real_escape_string($_POST['date("d/m/y H:i:s")']);

    $sql = sprintf("UPDATE information
                    SET summary='%s', datetime=NOW()
                    WHERE `author`= '%s' AND title = '%s' ",
                    $summary, $author, $title);

    $result = mysql_query($sql) or die(mysql_error());
    echo "The information has been updated.
           <a href="infoedit.php?cmd=edit&author=$author&title=$title">Care to continuing editing</a>
           or <a href="info.php?author=$author&title=$title">view the edited content</a>?";
}