php update script failing to work

Question

bellow is the code for a PHP edit script. I am aware it is not protected as it is an example however it doesn't seem to work, from what i can see syntactically it is OK but i may be missing something.

The code:

<?php
$connect = mysql_connect("localhost","root","");
if (!$connect){
die("Connection failed:" . mysql_error());
}
mysql_select_db("test",$connect);


if (isset($_POST['update'])){
$UpdateQuery = "UPDATE tbl_venues SET venue_id='$_POST[id]', venue_name='$_POST[name]', venue_description ='$_POST[desc]', venue_address ='$_POST[address]', venue_type ='$_POST[type]' WHERE venue_id='$_POST[hidden]'"; 
mysql_query($UpdateQuery, $connect);


};

$sql = "SELECT * FROM tbl_venues ORDER BY venue_id";
$mydata = mysql_query($sql,$connect);
echo "<table border=1>
<tr>
<th>Venue ID</th>
<th>Venue Name</th>
<th>Venue Description</th>
<th>Venue Address</th>
<th>Venue Type</th>
</tr>";
while($record = mysql_fetch_array($mydata)){

echo"<form action=venuelist.php method=post>";
echo "<tr>";
echo "<td><input type='text' name='id' value='" . $record['venue_id'] . "'> </td>";
echo "<td><input type='text' name='name' value='"  . $record['venue_name'] . "'> </td>";
echo "<td><input type='text' name='desc' value='"  . $record['venue_description'] . "'> </td>";
echo "<td><input type='text' name='address' value='"  . $record['venue_adress'] . "'> </td>";
echo "<td><input type='text' name='type' value='"  . $record['venue_type'] . "'> </td>";
echo "<td><input type='hidden' name='hidden' value='" . $record['venue_id'] . "'> </td>";
echo "<td><input type='submit' name='update' value='update' " . "'> </td>";
echo "</form>";
}

echo "</table>";
mysql_close($connect);
?>

It displays the data proving its not a connection issue however the data stops showing when a apostrophe is present in the row. The main issue is it refuses to update the field.

Any suggestions? Thanks

**By building SQL statements with outside variables, you are leaving yourself open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. [This question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. You can also see http://bobby-tables.com/php for alternatives and explanation of the danger. — Andy Lester, Apr 17 '14 at 21:28

score 1 · Answer 1 · answered Apr 17 '14 at 21:07

1

If the value includes an apostrophe it will break the html.

e.g. Your rendered html will look something like this:

<input type='hidden' name='hidden' value='it's a problem'>

Most frameworks will automatically prevent issues like this by automatically escaping problematic characters so you don't need to do it manually. (e.g. CakePHP and many others)

answered Apr 17 '14 at 21:07

pythonjsgeo

5,122
2
34
47

I see, is there a way around this? – Apr 17 '14 at 21:10
1

Working around this manually can be deceptively complicated and dangerous, from a security standpoint. I highly recommend using a framework which will take care of it automatically. – pythonjsgeo Apr 17 '14 at 21:13
Thanks I understand ill take a look, any ideas on why the update/edit function is not working? – Apr 17 '14 at 21:21
You will have the same issue in your sql update statement. e.g. "UPDATE tbl_venues SET venue_id='it's a problem'..." To help understand what is going on... a very hacky solution would be to use mysql_real_escape_string() (addslashes()) for creating the SQL and escape the apostrophe when generating the html. e.g. Replace the apostrohpe so your html looks like: . You can see how it quickly gets complicated. – pythonjsgeo Apr 17 '14 at 21:27

score 0 · Answer 2 · edited May 23 '17 at 12:11

Here's a method for escaping single quotes in your output. It uses the php function str_replace.

$myString = "testing 'this' is a problem";
$myString = str_replace('\'', '\\\'', $myString);
echo $myString;

As far as how to remove those single quotes on the way into the database, take a look at prepared statements. It's a reliable way to protect your database from SQL injection. In particular take a look at this canonical question and answer on the topic. Your update statement is not working because single quotes break the SQL query. So you'll have to deal with those some way, and prepared statements is the best practice there.

php update script failing to work

2 Answers2