The correct way to avoid SQL injection attacks, no matter which database you use, is to separate the data from SQL, so that data stays data and will never be interpreted as commands by the SQL parser. It is possible to create SQL statement with correctly formatted data parts, but if you don't fully understand the details, you should always use prepared statements and parameterized queries. These are SQL statements that are sent to and parsed by the database server separately from any parameters. This way it is impossible for an attacker to inject malicious SQL.
You basically have two options to achieve this:
Using PDO (for any supported database driver):
$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');
$stmt->execute([ 'name' => $name ]);
foreach ($stmt as $row) {
// Do something with $row
}
Using MySQLi (for MySQL):
$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');
$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
// Do something with $row
}
If you're connecting to a database other than MySQL, there is a driver-specific second option that you can refer to (for example, pg_prepare()
and pg_execute()
for PostgreSQL). PDO is the universal option.
Correctly setting up the connection
Note that when using PDO to access a MySQL database real prepared statements are not used by default. To fix this you have to disable the emulation of prepared statements. An example of creating a connection using PDO is:
$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');
$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
In the above example the error mode isn't strictly necessary, but it is advised to add it. This way the script will not stop with a Fatal Error
when something goes wrong. And it gives the developer the chance to catch
any error(s) which are throw
n as PDOException
s.
What is mandatory, however, is the first setAttribute()
line, which tells PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values aren't parsed by PHP before sending it to the MySQL server (giving a possible attacker no chance to inject malicious SQL).
Although you can set the charset
in the options of the constructor, it's important to note that 'older' versions of PHP (before 5.3.6) silently ignored the charset parameter in the DSN.
Explanation
The SQL statement you pass to prepare
is parsed and compiled by the database server. By specifying parameters (either a ?
or a named parameter like :name
in the example above) you tell the database engine where you want to filter on. Then when you call execute
, the prepared statement is combined with the parameter values you specify.
The important thing here is that the parameter values are combined with the compiled statement, not an SQL string. SQL injection works by tricking the script into including malicious strings when it creates SQL to send to the database. So by sending the actual SQL separately from the parameters, you limit the risk of ending up with something you didn't intend.
Any parameters you send when using a prepared statement will just be treated as strings (although the database engine may do some optimization so parameters may end up as numbers too, of course). In the example above, if the $name
variable contains 'Sarah'; DELETE FROM employees
the result would simply be a search for the string "'Sarah'; DELETE FROM employees"
, and you will not end up with an empty table.
Another benefit of using prepared statements is that if you execute the same statement many times in the same session it will only be parsed and compiled once, giving you some speed gains.
Oh, and since you asked about how to do it for an insert, here's an example (using PDO):
$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');
$preparedStatement->execute([ 'column' => $unsafeValue ]);
Can prepared statements be used for dynamic queries?
While you can still use prepared statements for the query parameters, the structure of the dynamic query itself cannot be parametrized and certain query features cannot be parametrized.
For these specific scenarios, the best thing to do is use a whitelist filter that restricts the possible values.
// Value whitelist
// $dir can only be 'DESC', otherwise it will be 'ASC'
if (empty($dir) || $dir !== 'DESC') {
$dir = 'ASC';
}
Best Answer
What are the syntax errors?
PHP belongs to the C-style and imperative programming languages. It has rigid grammar rules, which it cannot recover from when encountering misplaced symbols or identifiers. It can't guess your coding intentions.
Most important tips
There are a few basic precautions you can always take:
Use proper code indentation, or adopt any lofty coding style. Readability prevents irregularities.
Use an IDE or editor for PHP with syntax highlighting. Which also help with parentheses/bracket balancing.
Read the language reference and examples in the manual. Twice, to become somewhat proficient.
How to interpret parser errors
A typical syntax error message reads:
Which lists the possible location of a syntax mistake. See the mentioned file name and line number.
A moniker such as
T_STRING
explains which symbol the parser/tokenizer couldn't process finally. This isn't necessarily the cause of the syntax mistake, however.It's important to look into previous code lines as well. Often syntax errors are just mishaps that happened earlier. The error line number is just where the parser conclusively gave up to process it all.
Solving syntax errors
There are many approaches to narrow down and fix syntax hiccups.
Open the mentioned source file. Look at the mentioned code line.
For runaway strings and misplaced operators, this is usually where you find the culprit.
Read the line left to right and imagine what each symbol does.
More regularly you need to look at preceding lines as well.
In particular, missing
;
semicolons are missing at the previous line ends/statement. (At least from the stylistic viewpoint. )If
{
code blocks}
are incorrectly closed or nested, you may need to investigate even further up the source code. Use proper code indentation to simplify that.Look at the syntax colorization!
Strings and variables and constants should all have different colors.
Operators
+-*/.
should be tinted distinct as well. Else they might be in the wrong context.If you see string colorization extend too far or too short, then you have found an unescaped or missing closing
"
or'
string marker.Having two same-colored punctuation characters next to each other can also mean trouble. Usually, operators are lone if it's not
++
,--
, or parentheses following an operator. Two strings/identifiers directly following each other are incorrect in most contexts.Whitespace is your friend. Follow any coding style.
Break up long lines temporarily.
You can freely add newlines between operators or constants and strings. The parser will then concretize the line number for parsing errors. Instead of looking at the very lengthy code, you can isolate the missing or misplaced syntax symbol.
Split up complex
if
statements into distinct or nestedif
conditions.Instead of lengthy math formulas or logic chains, use temporary variables to simplify the code. (More readable = fewer errors.)
Add newlines between:
Partitioning up long code blocks really helps to locate the origin of syntax errors.
Comment out offending code.
If you can't isolate the problem source, start to comment out (and thus temporarily remove) blocks of code.
As soon as you got rid of the parsing error, you have found the problem source. Look more closely there.
Sometimes you want to temporarily remove complete function/method blocks. (In case of unmatched curly braces and wrongly indented code.)
When you can't resolve the syntax issue, try to rewrite the commented out sections from scratch.
As a newcomer, avoid some of the confusing syntax constructs.
The ternary
? :
condition operator can compact code and is useful indeed. But it doesn't aid readability in all cases. Prefer plainif
statements while unversed.PHP's alternative syntax (
if:
/elseif:
/endif;
) is common for templates, but arguably less easy to follow than normal{
code}
blocks.The most prevalent newcomer mistakes are:
Missing semicolons
;
for terminating statements/lines.Mismatched string quotes for
"
or'
and unescaped quotes within.Forgotten operators, in particular for the string
.
concatenation.Unbalanced
(
parentheses)
. Count them in the reported line. Are there an equal number of them?Don't forget that solving one syntax problem can uncover the next.
If you make one issue go away, but other crops up in some code below, you're mostly on the right path.
If after editing a new syntax error crops up in the same line, then your attempted change was possibly a failure. (Not always though.)
Restore a backup of previously working code, if you can't fix it.
diff
of the broken and last working version. Which might be enlightening as to what the syntax problem is.Invisible stray Unicode characters: In some cases, you need to use a hexeditor or different editor/viewer on your source. Some problems cannot be found just from looking at your code.
Try
grep --color -P -n "\[\x80-\xFF\]" file.php
as the first measure to find non-ASCII symbols.In particular BOMs, zero-width spaces, or non-breaking spaces, and smart quotes regularly can find their way into the source code.
Take care of which type of linebreaks are saved in files.
PHP just honors \n newlines, not \r carriage returns.
Which is occasionally an issue for MacOS users (even on OS X for misconfigured editors).
It often only surfaces as an issue when single-line
//
or#
comments are used. Multiline/*...*/
comments do seldom disturb the parser when linebreaks get ignored.If your syntax error does not transmit over the web: It happens that you have a syntax error on your machine. But posting the very same file online does not exhibit it anymore. Which can only mean one of two things:
You are looking at the wrong file!
Or your code contained invisible stray Unicode (see above). You can easily find out: Just copy your code back from the web form into your text editor.
Check your PHP version. Not all syntax constructs are available on every server.
php -v
for the command line interpreter<?php phpinfo();
for the one invoked through the webserver.Those aren't necessarily the same. In particular when working with frameworks, you will them to match up.
Don't use PHP's reserved keywords as identifiers for functions/methods, classes or constants.
Trial-and-error is your last resort.
If all else fails, you can always google your error message. Syntax symbols aren't as easy to search for (Stack Overflow itself is indexed by SymbolHound though). Therefore it may take looking through a few more pages before you find something relevant.
Further guides:
White screen of death
If your website is just blank, then typically a syntax error is the cause. Enable their display with:
error_reporting = E_ALL
display_errors = 1
In your
php.ini
generally, or via.htaccess
for mod_php, or even.user.ini
with FastCGI setups.Enabling it within the broken script is too late because PHP can't even interpret/run the first line. A quick workaround is crafting a wrapper script, say
test.php
:Then invoke the failing code by accessing this wrapper script.
It also helps to enable PHP's
error_log
and look into your webserver'serror.log
when a script crashes with HTTP 500 responses.