Crash Course in PHP and MySQLi

Making the initial connection

Creates the new mysqli object. ‘localhost’ is the most commonly used host for MySQL. However some shared hosting providers may force you to use their own external MySQL servers.

This checks to see if a connection error occurred and kills the script and prints out an error message. In a production environment you would never want to print out the actual error, instead you should log it and only display a very generic error to the user.

Querying some data

Here I’ll demonstrate how to query some data and write it to the page. The following query selects the first 10 rows from the cities table.

Updating a field in a table

Retrieving user input, escaping it, and finally inserting it

Here we’ll take some data sent in from the browser, presumably form input, and escape it. All input from the user should be escaped before putting it into a query, otherwise you risk being the victim of an SQL injection attack. Another method to avoid SQL injection is to use prepared queries, which I’ll demonstrate further on.

The first line checks to make sure the $_POST variable exists, and then escapes the input. The input is then checked to see if it is empty or not and inserted.

Using a prepared statement to insert data

The following would show how to do the same query as above, only using a prepared statement. No escaping is needed since it’s done by the prepared query itself.

Using a prepared statement to query data

The following shows how to take some user input, create a prepared query, and fetch the data.

Updating multiple fields based a user ID.

Closing up

The following usually isn’t needed seeing as how the connection is closed as soon as the page is executed, but if you’re that worried you can put in cleanup code for your site.

Common Mistakes and Tips

  • If you’re encountering access denied errors while connecting and you’re sure you have the right username and password, make certain that the user you are trying to connect with has the correct host and database permissions. Your host also might require that you white list the IP you are attempting to connect from.
  • Sanitize your user input as much as possible, and don’t think escaping data alone will protect you in all cases. For instance if you have a user ID field that you know for certain is only going to be numerical, use preg_replace to filter out bad characters, or use the built-in PHP filtering functions.
  • Never store passwords or other confidential data as plaintext in the database, always use a secure seeded hashing method and encryption. This document describes secure methods you should be hashing passwords in PHP. You also shouldn’t be using MD5 or SHA1 by themselves, as they are no longer deemed safe.
  • Try to verify that queries actually succeeded rather than blindly sending queries and assuming they succeeded. Query errors can often be the first sign of trouble, especially when under load.
  • Never display SQL errors or other PHP errors to a user in a production environment, this can easily give an attacker vital information he or she needs to infiltrate your site.
  • For other security tips visit OWASP.