Reference: What is a perfect code sample using the MySQL extension?

This is to create a community learning resource. The goal is to have examples of good code that do not repeat the awful mistakes that can so often be found in copy/pasted PHP code. I have requested it be made Community Wiki.

This is not meant as a coding contest. It's not about finding the fastest or most compact way to do a query - it's to provide a good, readable reference especially for newbies.

Every day, there is a huge influx of questions with really bad code snippets using the mysql_* family of functions on Stack Overflow. While it is usually best to direct those people towards PDO, it sometimes is neither possible (e.g. inherited legacy software) nor a realistic expectation (users are already using it in their project).

Common problems with code using the mysql_* library include:

SQL injection in values
SQL injection in LIMIT clauses and dynamic table names
No error reporting ("Why does this query not work?")
Broken error reporting (that is, errors always occur even when the code is put into production)
Cross-site scripting (XSS) injection in value output

Let's write a PHP code sample that does the following using the mySQL_* family of functions:

Accept two POST values, id (numeric) and name (a string)
Do an UPDATE query on a table tablename, changing the name column in the row with the ID id
On failure, exit graciously, but show the detailed error only in production mode. trigger_error() will suffice; alternatively use a method of your choosing
Output the message "$name updated."

And does not show any of the weaknesses listed abo

Edit Report

<?  
mysql_connect(); 
mysql_select_db("new"); 
$table = "test"; 
if($_SERVER['REQUEST_METHOD']=='POST') {
  $name = mysql_real_escape_string($_POST['name']); 
  if ($id = intval($_POST['id'])) { 
    $query="UPDATE $table SET name='$name' WHERE id=$id"; 
  } else { 
    $query="INSERT INTO $table SET name='$name'"; 
  } 
  mysql_query($query) or trigger_error(mysql_error()." in ".$query); 
  header("Location: http://".$_SERVER['HTTP_HOST'].$_SERVER['PHP_SELF']);  
  exit;  
}  
if (!isset($_GET['id'])) {
  $LIST=array(); 
  $query="SELECT * FROM $table";  
  $res=mysql_query($query); 
  while($row=mysql_fetch_assoc($res)) $LIST[]=$row; 
  include 'list.php'; 
} else {
  if ($id=intval($_GET['id'])) { 
    $query="SELECT * FROM $table WHERE id=$id";  
    $res=mysql_query($query); 
    $row=mysql_fetch_assoc($res); 
    foreach ($row as $k => $v) $row[$k]=htmlspecialchars($v); 
  } else { 
    $row['name']=''; 
    $row['id']=0; 
  } 
  include 'form.php'; 
}  
?>

form.php

<? include 'tpl_top.php' ?>
<form method="POST">
<input type="text" name="name" value="<?=$row['name']?>"><br>
<input type="hidden" name="id" value="<?=$row['id']?>">
<input type="submit"><br>
<a href="?">Return to the list</a>
</form>
<? include 'tpl_bottom.php' ?>

list.php

<? include 'tpl_top.php' ?>
<a href="?id=0">Add item</a>
<? foreach ($LIST as $row): ?>
<li><a href="?id=<?=$row['id']?>"><?=$row['name']?></a>
<? endforeach ?>
<? include 'tpl_bottom.php' ?>

answered 2012-01-15T16:49:50.593

Reference: What is a perfect code sample using the MySQL extension?

1 Answer

Your Answer