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.
|
1 |
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'my_db'); |
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.
|
1 2 3 |
if ($mysqli->connect_error) { die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error); } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
if ($result = $mysqli->query("SELECT `name` FROM `cities` LIMIT 10;")) { // print the number of returned rows printf("Select returned %d rows.\n", $result->num_rows); print("The first 10 cities are\n"); // fetch_assoc() returns the row as an array using the column name as the associative key while($row = $result->fetch_assoc()) { printf("%s\n", $row['city']); } // free result set $result->close(); } |
Updating a field in a table
|
1 2 3 |
if($mysqli->query("UPDATE `stats` SET `views` = `views` + 1;") === TRUE) { printf("Updated site views count successfully!\n"); } |
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.
|
1 2 3 4 5 6 7 |
$input_city = isset($_POST['city']) ? $mysqli->real_escape_string($_POST['city']) : ''; if(!empty($input_city)) { // now lets insert the row. if ($mysqli->query("INSERT INTO `cities` (`name`) VALUES ('$city');")) { printf("%d Row inserted.\n", $mysqli->affected_rows); } } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$input_city = isset($_POST['city']) ? $_POST['city'] : ''; if(!empty($input_city)) { // prepares the statement and returns a statement if ($stmt = $mysqli->prepare("INSERT INTO `cities` (`name`) VALUES (?);")) { // bind parameters for this query $stmt->bind_param("s", $input_city); // execute query if($stmt->execute()) { printf("%d row(s) inserted.\n", $stmt->affected_rows); } } } |
Using a prepared statement to query data
The following shows how to take some user input, create a prepared query, and fetch the data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
$input_zipcode = isset($_POST['zipcode']) ? $_POST['zipcode'] : ''; if ($stmt = $mysqli->prepare("SELECT `name`, `zipcode` FROM `cities` WHERE `zipcode` = ?;")) { // bind parameters for markers $stmt->bind_param("i", $input_zipcode); // execute query $stmt->execute(); // instead of bind_result $result = $stmt->get_result(); // fetch the results into an array */ while ($row = $result->fetch_assoc()) { printf("%s is a part of the zipcode %d\n", $row['name'], $row['zipcode']); } // close statement $stmt->close(); } |
Updating multiple fields based a user ID.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
$input_name = isset($_POST['name']) ? $mysqli->real_escape_string($_POST['name']) : ''; $input_email = isset($_POST['email']) ? $mysqli->real_escape_string($_POST['email']) : ''; $input_aboutme = isset($_POST['aboutme']) ? $mysqli->real_escape_string($_POST['aboutme']) : ''; // this assumes you are storing the currently logged in user in a session variable // and that you have some kind of authentication before this point. $user_id = $_SESSION['user_id']; if(!empty($input_name) || !empty($input_email)) { // now lets insert the row. if ($mysqli->query("UPDATE `profile` SET `name` = '$input_name', `email` = '$input_email', `aboutme' = '$input_aboutme' WHERE `user_id` = $user_id;")) { printf("%d Row inserted.\n", $mysqli->affected_rows); } } |
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.
|
1 |
$mysqli->close(); |
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.