When users fill out forms on your website—like leaving a guestbook entry—it’s easy to assume their input is harmless. But what if someone tries to manipulate that input to damage your database? That’s where SQL injection comes in.
The problem lies in the following instruction:
$db->query( "
INSERT INTO entries (entry) VALUES ('$entry')");
As a reminder: The value of $entry is transferred via POST. So far, so good, but what happens if the entry contains an apostrophe, such as Shaquille O'Neal? Then the SQL command would look like this:
INSERT INTO entries (entry) VALUES ('Shaquille O'Neal')
As you can easily see, the SQL command is invalid. But that's not so bad. What do you think of the following command?
INSERT INTO entries (entry) VALUES (''); DELETE FROM entries --')
Here, an (empty) entry is inserted into the database and then the database content is deleted completely. The two hyphens are a SQL comment; that is, everything after them is ignored. This would be a disaster for the website as all guestbook entries would be gone in one fell swoop. But is it even possible to inject such a statement into our script?
Yes, it is. Here you can see the SQL command again, with a section highlighted in bold:
INSERT INTO entries (entry) VALUES (''); DELETE FROM entries --')
Everything that is not in bold is a SQL command in the PHP script. Everything that is written in bold would have to be inserted via a form, and the mishap has already happened. But what can be done about it? One possibility is to double all apostrophes:
$entry = str_replace("'", "''", $entry);
This is a first approach, but there are also other special characters in SQL, such as the underscore or the percent sign (both for WHERE clauses). It is therefore necessary to take special measures. For MySQL, magic_quotes also used to do this. SQL injection was one of the main reasons for the creation of the unpopular (and recently abolished) magic quotes functionality.
MySQL can cope with special characters being escaped by a backslash, but this is not part of the SQL standard. It is therefore not surprising that other databases do not interpret this as desired. But don't despair: Some database modules offer extra functions (or, in the case of an OOP API, methods) for preparing user input accordingly. This table shows a selection.
| Module | Function |
| MySQL | mysqli_real_escape_string()/MySQLi::real_escape"string() |
| SQLite | SQLite3::escapeString() |
| MSSQL | - |
| PostgreSQL | pg_escape_string() |
| Oracle | - |
As you can see, things look rather bleak for some database systems—but fortunately, these offer the preferred prepared statements as a secure approach anyway. The following listing shows an example in MySQL using the mysqli extension of PHP.
<?php
$entry = $_POST["entry"] ?? "";
if ($db = mysqli_connect("server", "user", "password",
"guestbook")) {
if ($stmt = mysqli_prepare($db,
"INSERT INTO entries (entry) VALUES (?)")) {
mysqli_stmt_bind_param($stmt, "s", $entry);
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
}
mysqli_close($db);
}
?>
<html>
<head>
<title>Guestbook</title>
</head>
<body>
<form method="post" action="">
Comment: <textarea name="entry" cols="" rows=""></textarea><br />
<input type="submit" value="Enter" />
</form>
</body>
</html>
SQL injection is particularly bad because it can cause serious damage to the web server. So be careful with every single database query in which you process user input. Even specialist magazines often contain code that does not filter external data and would therefore be susceptible to SQL injection.
You can safely test this yourself on your website. If you have pages where data is transferred via a URL (e.g., news.php?id=123), insert an apostrophe (news.php?id='123). If you receive a PHP error message, there are two potential danger points:
Editor’s note: This post has been adapted from a section of the book PHP and MySQL: The Comprehensive Guide by Christian Wenz and Tobias Hauser. Christian is a consultant and trainer who specializes in web technologies and web security. He leads digitization projects in corporate environments. His books have been translated into more than a dozen languages. Tobias is a consultant, trainer, and author. He supports companies with his focus on PHP-based web applications, covering everything from system selection to interface architecture, and writes regularly about web topics.
This post was originally published 6/2025.