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';
}
The sorted()
function takes a key=
parameter
newlist = sorted(list_to_be_sorted, key=lambda d: d['name'])
Alternatively, you can use operator.itemgetter
instead of defining the function yourself
from operator import itemgetter
newlist = sorted(list_to_be_sorted, key=itemgetter('name'))
For completeness, add reverse=True
to sort in descending order
newlist = sorted(list_to_be_sorted, key=itemgetter('name'), reverse=True)
Best Answer
Basic one dimensional arrays
Applicable sort functions:
sort
rsort
asort
arsort
natsort
natcasesort
ksort
krsort
The difference between those is merely whether key-value associations are kept (the "
a
" functions), whether it sorts low-to-high or reverse ("r
"), whether it sorts values or keys ("k
") and how it compares values ("nat
" vs. normal). See http://php.net/manual/en/array.sorting.php for an overview and links to further details.Multi dimensional arrays, including arrays of objects
If you want to sort
$array
by the key 'foo' of each entry, you need a custom comparison function. The abovesort
and related functions work on simple values that they know how to compare and sort. PHP does not simply "know" what to do with a complex value likearray('foo' => 'bar', 'baz' => 42)
though; so you need to tell it.To do that, you need to create a comparison function. That function takes two elements and must return
0
if these elements are considered equal, a value lower than0
if the first value is lower and a value higher than0
if the first value is higher. That's all that's needed:Often, you will want to use an anonymous function as the callback. If you want to use a method or static method, see the other ways of specifying a callback in PHP.
You then use one of these functions:
usort
uasort
uksort
Again, they only differ in whether they keep key-value associations and sort by values or keys. Read their documentation for details.
Example usage:
usort
will take two items from the array and call yourcmp
function with them. Socmp()
will be called with$a
asarray('foo' => 'bar', 'baz' => 42)
and$b
as anotherarray('foo' => ..., 'baz' => ...)
. The function then returns tousort
which of the values was larger or whether they were equal.usort
repeats this process passing different values for$a
and$b
until the array is sorted. Thecmp
function will be called many times, at least as many times as there are values in$array
, with different combinations of values for$a
and$b
every time.To get used to this idea, try this:
All you did was define a custom way to compare two items, that's all you need. That works with all sorts of values.
By the way, this works on any value, the values don't have to be complex arrays. If you have a custom comparison you want to do, you can do it on a simple array of numbers too.
sort
sorts by reference and does not return anything useful!Note that the array sorts in place, you do not need to assign the return value to anything.
$array = sort($array)
will replace the array withtrue
, not with a sorted array. Justsort($array);
works.Custom numeric comparisons
If you want to sort by the
baz
key, which is numeric, all you need to do is:Thanks to The PoWEr oF MATH this returns a value < 0, 0 or > 0 depending on whether
$a
is lower than, equal to or larger than$b
.Note that this won't work well for
float
values, since they'll be reduced to anint
and lose precision. Use explicit-1
,0
and1
return values instead.Objects
If you have an array of objects, it works the same way:
Functions
You can do anything you need inside a comparison function, including calling functions:
Strings
A shortcut for the first string comparison version:
strcmp
does exactly what's expected ofcmp
here, it returns-1
,0
or1
.Spaceship operator
PHP 7 introduced the spaceship operator, which unifies and simplifies equal/smaller/larger than comparisons across types:
Sorting by multiple fields
If you want to sort primarily by
foo
, but iffoo
is equal for two elements sort bybaz
:For those familiar, this is equivalent to an SQL query with
ORDER BY foo, baz
.Also see this very neat shorthand version and how to create such a comparison function dynamically for an arbitrary number of keys.
Sorting into a manual, static order
If you want to sort elements into a "manual order" like "foo", "bar", "baz":
For all the above, if you're using PHP 5.3 or higher (and you really should), use anonymous functions for shorter code and to avoid having another global function floating around:
That's how simple sorting a complex multi-dimensional array can be. Again, just think in terms of teaching PHP how to tell which of two items is "greater"; let PHP do the actual sorting.
Also for all of the above, to switch between ascending and descending order simply swap the
$a
and$b
arguments around. E.g.:Sorting one array based on another
And then there's the peculiar
array_multisort
, which lets you sort one array based on another:The expected result here would be:
Use
array_multisort
to get there:As of PHP 5.5.0 you can use
array_column
to extract a column from a multi dimensional array and sort the array on that column:You can also sort on more than one column each in either direction:
As of PHP 7.0.0 you can also extract properties from an array of objects.