MySQL Prepared Statements

by

Use PDO (PHP Data Objects) to connect to your MySQL database. This method will make sure that all database input will always be treated as text strings and you will never have to do any manual escaping.

This combined with proper use of html_entities() to display data from your database is a solid and good way to protect your page from injection. I always use PDO to handle all my database connections in my projects.

Create database object (and in this case enforce a certain character encoding):

try {
    $db = new PDO("mysql:host=[hostname];dbname=[database]",'[username]','[password]');
    $db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES utf8");
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $db->exec('SET NAMES utf8');
} catch (PDOException $e) {
    echo $e->getMessage();
}

Then use it like so:

$id = 1;
$q = $db->prepare('SELECT * FROM Table WHERE id = ?');
$q->execute(array($id));
$row = $q->fetch();
echo $row['Column_1'];

or

$q = $db->prepare('UPDATE Table SET Column_1 = ?, Column_2 = ? WHERE id = ?');
$q->execute(array('Value for Column_1','Value for Column_2',$id));

and with wildcards:

$search="John";
$q = $db->prepare('SELECT * FROM Table WHERE Column_1 LIKE ?');
$q->execute(array('%'.$search.'%'));
$num = $q->rowCount();
if ($num > 0) {
  while ($row = $q->fetch()) {
    echo $row['Column_1'];
  }
} else {
  echo "No hits!";
}

Read more:

How can I prevent SQL injection in PHP?

When *not* to use prepared statements?

how safe are PDO prepared statements

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

More Related Contents:

  1. How can I prevent SQL injection in PHP?
  2. SQL injection that gets around mysql_real_escape_string()
  3. Is “mysqli_real_escape_string” enough to avoid SQL injection or other SQL attacks?
  4. Reference: What is a perfect code sample using the MySQL extension? [closed]
  5. SQL injections in ADOdb and general website security
  6. Is mysql_real_escape_string() broken?
  7. Why is using a mysql prepared statement more secure than using the common escape functions?
  8. Does mysql_real_escape_string() FULLY protect against SQL injection?
  9. how safe are PDO prepared statements
  10. Shortcomings of mysql_real_escape_string?
  11. Do I have to guard against SQL injection if I used a dropdown?
  12. Do I have to use mysql_real_escape_string if I bind parameters?
  13. Is mysql_real_escape_string enough to Anti SQL Injection?
  14. What is the PDO equivalent of function mysql_real_escape_string?
  15. How to create this type of JSON data from PHP [closed]
  16. Create hyperlink from html table row to open record on a new page
  17. SELECT COUNT(*) AS count – How to use this count
  18. How to execute two mysql queries as one in PHP/MYSQL?
  19. How to upload images into MySQL database using PHP code
  20. selecting unique values from a column
  21. MySQL – Access denied for user [duplicate]
  22. Calling stored procedure with Out parameter using PDO
  23. How to display an BLOB image stored in MySql database?
  24. How to bind LIKE values using the PDO extension?
  25. How do I set the selected item in a drop down box
  26. Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  27. WordPress Fatal error: Uncaught Error: Call to undefined function mysql_connect() in /wp-includes/wp-db.php:1570
  28. Strange problem with the length of the field gotten from database [duplicate]
  29. Can I detect and handle MySQL Warnings with PHP?
  30. mysql count into PHP variable

Leave a Comment