security/database.xml
7204e2dbb9b484c8b67bb5ad4a93fa1369c5b317
...
...
@@ -89,8 +89,8 @@
89
89
The easiest way to work around this problem is to first create your own
90
90
encryption package, and then use it from within your <acronym>PHP</acronym> scripts. <acronym>PHP</acronym>
91
91
can assist you in this with several extensions, such as <link
92
-
linkend="ref.mcrypt">Mcrypt</link> and <link
93
-
linkend="ref.mhash">Mhash</link>, covering a wide variety of encryption
92
+
linkend="book.openssl">OpenSSL</link> and <link
93
+
linkend="book.sodium">Sodium</link>, covering a wide variety of encryption
94
94
algorithms. The script encrypts the data before inserting it into the database, and decrypts
95
95
it when retrieving. See the references for further examples of how
96
96
encryption works.
...
...
@@ -105,10 +105,8 @@
105
105
password in a database, instead of the password itself.
106
106
</simpara>
107
107
<simpara>
108
-
In PHP 5.5 or newer <link linkend="ref.password">password</link> functions
108
+
The <link linkend="ref.password">password</link> functions
109
109
provide a convenient way to hash sensitive data and work with these hashes.
110
-
In PHP 5.3.7+ <link xmlns:xlink="http://www.w3.org/1999/xlink" xlink:href="&url.password.compat;">
111
-
password_compat</link> library can also be used.
112
110
</simpara>
113
111
<simpara>
114
112
<function>password_hash</function> is used to hash a given string using the
...
...
@@ -142,72 +140,35 @@ if ($row && password_verify($password, $row['pwd'])) {
142
140
]]>
143
141
</programlisting>
144
142
</example>
145
-
<simpara>
146
-
In older versions of PHP this can be achieved using <function>crypt</function>
147
-
function.
148
-
</simpara>
149
-
<example>
150
-
<title>Hashing password using <function>crypt</function>s</title>
151
-
<programlisting role="php">
152
-
<![CDATA[
153
-
<?php
154
-

155
-
// storing password hash
156
-
// $random_chars retrieved e.g. using /dev/random
157
-
$query = sprintf("INSERT INTO users(name,pwd) VALUES('%s','%s');",
158
-
pg_escape_string($username),
159
-
pg_escape_string(crypt($password, '$2a$07$' . $random_chars . '$')));
160
-
$result = pg_query($connection, $query);
161
-

162
-
// querying if user submitted the right password
163
-
$query = sprintf("SELECT pwd FROM users WHERE name='%s';",
164
-
pg_escape_string($username));
165
-
$row = pg_fetch_assoc(pg_query($connection, $query));
166
-

167
-
if ($row && crypt($password, $row['pwd']) == $row['pwd']) {
168
-
echo 'Welcome, ' . htmlspecialchars($username) . '!';
169
-
} else {
170
-
echo 'Authentication failed for ' . htmlspecialchars($username) . '.';
171
-
}
172
-

173
-
?>
174
-
]]>
175
-
</programlisting>
176
-
</example>
177
143
</sect2>
178
144
</sect1>
179
145

180
146
<sect1 xml:id="security.database.sql-injection">
181
147
<title>SQL Injection</title>
182
148
<simpara>
183
-
Many web developers are unaware of how SQL queries can be tampered with,
184
-
and assume that an SQL query is a trusted command. It means that SQL
185
-
queries are able to circumvent access controls, thereby bypassing standard
186
-
authentication and authorization checks, and sometimes SQL queries even
187
-
may allow access to host operating system level commands.
188
-
</simpara>
189
-
<simpara>
190
-
Direct SQL Command Injection is a technique where an attacker creates or
191
-
alters existing SQL commands to expose hidden data, or to override valuable
192
-
ones, or even to execute dangerous system level commands on the database
193
-
host. This is accomplished by the application taking user input and
194
-
combining it with static parameters to build an SQL query. The following
195
-
examples are based on true stories, unfortunately.
149
+
SQL injection is a technique where an attacker exploits flaws in
150
+
application code responsible for building dynamic SQL queries.
151
+
The attacker can gain access to privileged sections of the application,
152
+
retrieve all information from the database, tamper with existing data,
153
+
or even execute dangerous system-level commands on the database
154
+
host. The vulnerability occurs when developers concatenate or
155
+
interpolate arbitrary input in their SQL statements.
196
156
</simpara>
197
157
<para>
198
-
Owing to the lack of input validation and connecting to the database on
199
-
behalf of a superuser or the one who can create users, the attacker
200
-
may create a superuser in your database.
201
158
<example>
202
159
<title>
203
160
Splitting the result set into pages ... and making superusers
204
161
(PostgreSQL)
205
162
</title>
163
+
<simpara>
164
+
In the following example, user input is directly interpolated into the
165
+
SQL query allowing the attacker to gain a superuser account in the database.
166
+
</simpara>
206
167
<programlisting role="php">
207
168
<![CDATA[
208
169
<?php
209
170

210
-
$offset = $argv[0]; // beware, no input validation!
171
+
$offset = $_GET['offset']; // beware, no input validation!
211
172
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
212
173
$result = pg_query($conn, $query);
213
174

...
...
@@ -217,9 +178,8 @@ $result = pg_query($conn, $query);
217
178
</example>
218
179
Normal users click on the 'next', 'prev' links where the <varname>$offset</varname>
219
180
is encoded into the <acronym>URL</acronym>. The script expects that the incoming
220
-
<varname>$offset</varname> is a decimal number. However, what if someone tries to
221
-
break in by appending a <function>urlencode</function>'d form of the
222
-
following to the <acronym>URL</acronym>
181
+
<varname>$offset</varname> is a number. However, what if someone tries to
182
+
break in by appending the following to the <acronym>URL</acronym>
223
183
<informalexample>
224
184
<programlisting role="sql">
225
185
<![CDATA[
...
...
@@ -231,13 +191,13 @@ insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)
231
191
]]>
232
192
</programlisting>
233
193
</informalexample>
234
-
If it happened, then the script would present a superuser access to him.
194
+
If it happened, the script would present a superuser access to the attacker.
235
195
Note that <literal>0;</literal> is to supply a valid offset to the
236
196
original query and to terminate it.
237
197
</para>
238
198
<note>
239
199
<para>
240
-
It is common technique to force the SQL parser to ignore the rest of the
200
+
It is a common technique to force the SQL parser to ignore the rest of the
241
201
query written by the developer with <literal>--</literal> which is the
242
202
comment sign in SQL.
243
203
</para>
...
...
@@ -250,8 +210,8 @@ insert into pg_shadow(usename,usesysid,usesuper,usecatupd,passwd)
250
210
LIMIT</literal> and <literal>OFFSET</literal> clauses in <literal>SELECT</literal>
251
211
statements. If your database supports the <literal>UNION</literal> construct,
252
212
the attacker may try to append an entire query to the original one to list
253
-
passwords from an arbitrary table. Using encrypted password fields is
254
-
strongly encouraged.
213
+
passwords from an arbitrary table. It is strongly recommended to store only
214
+
secure hashes of passwords instead of the passwords themselves.
255
215
<example>
256
216
<title>
257
217
Listing out articles ... and some passwords (any database server)
...
...
@@ -279,18 +239,10 @@ union select '1', concat(uname||'-'||passwd) as name, '1971-01-01', '0' from use
279
239
]]>
280
240
</programlisting>
281
241
</informalexample>
282
-
If this query (playing with the <literal>'</literal> and
283
-
<literal>--</literal>) were assigned to one of the variables used in
284
-
<varname>$query</varname>, the query beast awakened.
285
242
</para>
286
243
<para>
287
-
SQL UPDATE's are also susceptible to attack. These queries are
288
-
also threatened by chopping and appending an entirely new query to it. But
289
-
the attacker might fiddle with the <literal>SET</literal> clause. In this
290
-
case some schema information must be possessed to manipulate the query
291
-
successfully. This can be acquired by examining the form variable names, or
292
-
just simply brute forcing. There are not so many naming conventions for
293
-
fields storing passwords or usernames.
244
+
<literal>UPDATE</literal> and <literal>INSERT</literal> statements are also
245
+
susceptible to such attacks.
294
246
<example>
295
247
<title>
296
248
From resetting a password ... to gaining more privileges (any database server)
...
...
@@ -303,11 +255,11 @@ $query = "UPDATE usertable SET pwd='$pwd' WHERE uid='$uid';";
303
255
]]>
304
256
</programlisting>
305
257
</example>
306
-
But if a malicious user submits the value
258
+
If a malicious user submits the value
307
259
<literal>' or uid like'%admin%</literal> to <varname>$uid</varname> to
308
260
change the admin's password, or simply sets <varname>$pwd</varname> to
309
261
<literal>hehehe', trusted=100, admin='yes</literal> to gain more
310
-
privileges, then, the query will be twisted:
262
+
privileges, then the query will be twisted:
311
263
<informalexample>
312
264
<programlisting role="php">
313
265
<![CDATA[
...
...
@@ -325,11 +277,25 @@ $query = "UPDATE usertable SET pwd='hehehe', trusted=100, admin='yes' WHERE
325
277
</programlisting>
326
278
</informalexample>
327
279
</para>
280
+
<simpara>
281
+
While it remains obvious that an attacker must possess at least some
282
+
knowledge of the database architecture to conduct a successful
283
+
attack, obtaining this information is often very simple. For example,
284
+
the code may be part of an open-source software and be publicly available.
285
+
This information may also be divulged
286
+
by closed-source code - even if it's encoded, obfuscated, or compiled -
287
+
and even by your own code through the display of error messages.
288
+
Other methods include the use of typical table and column names. For
289
+
example, a login form that uses a 'users' table with column names
290
+
'id', 'username', and 'password'.
291
+
</simpara>
328
292
<para>
329
-
A frightening example of how operating system level commands can be accessed
330
-
on some database hosts.
331
293
<example>
332
-
<title>Attacking the database hosts operating system (MSSQL Server)</title>
294
+
<title>Attacking the database host operating system (MSSQL Server)</title>
295
+
<simpara>
296
+
A frightening example of how operating system-level commands can be
297
+
accessed on some database hosts.
298
+
</simpara>
333
299
<programlisting role="php">
334
300
<![CDATA[
335
301
<?php
...
...
@@ -360,137 +326,140 @@ $result = mssql_query($query);
360
326
</informalexample>
361
327
MSSQL Server executes the SQL statements in the batch including a command
362
328
to add a new user to the local accounts database. If this application
363
-
were running as <literal>sa</literal> and the MSSQLSERVER service is
329
+
were running as <literal>sa</literal> and the MSSQLSERVER service was
364
330
running with sufficient privileges, the attacker would now have an
365
331
account with which to access this machine.
366
332
</para>
367
333
<note>
368
334
<para>
369
-
Some of the examples above is tied to a specific database server. This
335
+
Some examples above are tied to a specific database server, but it
370
336
does not mean that a similar attack is impossible against other products.
371
337
Your database server may be similarly vulnerable in another manner.
372
338
</para>
373
339
</note>
374
340
<para>
375
341
<mediaobject>
376
-
<alt>A worked example of the issues regarding SQL Injection</alt>
342
+
<alt>A funny example of the issues regarding SQL injection</alt>
377
343
<imageobject>
378
344
<imagedata fileref="en/security/figures/xkcd-bobby-tables.png" format="PNG"/>
379
345
</imageobject>
346
+
<caption>
347
+
<simpara>
348
+
Image courtesy of <link xlink:href="&url.xkcd;327">xkcd</link>
349
+
</simpara>
350
+
</caption>
380
351
</mediaobject>
381
-
Image courtesy of <link xlink:href="&url.xkcd;327">xkcd</link>
382
352
</para>
383
353

384
354
<sect2 xml:id="security.database.avoiding">
385
355
<title>Avoidance Techniques</title>
386
-
<simpara>
387
-
While it remains obvious that an attacker must possess at least some
388
-
knowledge of the database architecture in order to conduct a successful
389
-
attack, obtaining this information is often very simple. For example,
390
-
if the database is part of an open source or other publicly-available
391
-
software package with a default installation, this information is
392
-
completely open and available. This information may also be divulged
393
-
by closed-source code - even if it's encoded, obfuscated, or compiled -
394
-
and even by your very own code through the display of error messages.
395
-
Other methods include the user of common table and column names. For
396
-
example, a login form that uses a 'users' table with column names
397
-
'id', 'username', and 'password'.
398
-
</simpara>
399
-
<simpara>
400
-
These attacks are mainly based on exploiting the code not being written
401
-
with security in mind. Never trust any kind of input, especially that
402
-
which comes from the client side, even though it comes from a select box,
403
-
a hidden input field or a cookie. The first example shows that such a
404
-
blameless query can cause disasters.
405
-
</simpara>
406
-

407
-
<itemizedlist>
408
-
<listitem>
409
-
<simpara>
410
-
Never connect to the database as a superuser or as the database owner.
411
-
Use always customized users with very limited privileges.
412
-
</simpara>
413
-
</listitem>
414
-
<listitem>
415
-
<simpara>
416
-
Use prepared statements with bound variables. They are provided
417
-
<link linkend="pdo.prepared-statements">by PDO</link>,
418
-
<link linkend="mysqli.quickstart.prepared-statements">by MySQLi</link>
419
-
and by other libraries.
420
-
</simpara>
421
-
</listitem>
422
-
<listitem>
423
-
<simpara>
424
-
Check if the given input has the expected data type. <acronym>PHP</acronym> has
425
-
a wide range of input validating functions, from the simplest ones
426
-
found in <link linkend="ref.var">Variable Functions</link> and
427
-
in <link linkend="ref.ctype">Character Type Functions</link>
428
-
(e.g. <function>is_numeric</function>, <function>ctype_digit</function>
429
-
respectively) and onwards to the
430
-
<link linkend="ref.pcre">Perl compatible Regular Expressions</link>
431
-
support.
432
-
</simpara>
433
-
</listitem>
434
-
<listitem>
435
-
<para>
436
-
If the application waits for numerical input, consider verifying data
437
-
with <function>ctype_digit</function>, or silently change its type
438
-
using <function>settype</function>, or use its numeric representation
439
-
by <function>sprintf</function>.
440
-
<example>
441
-
<title>A more secure way to compose a query for paging</title>
442
-
<programlisting role="php">
356
+
<para>
357
+
The recommended way to avoid SQL injection is by binding all data via
358
+
prepared statements. Using parameterized queries isn't enough to entirely
359
+
avoid SQL injection, but it is the easiest and safest way to provide input
360
+
to SQL statements. All dynamic data literals in <literal>WHERE</literal>,
361
+
<literal>SET</literal>, and <literal>VALUES</literal> clauses must be
362
+
replaced with placeholders. The actual data will be bound during the
363
+
execution and sent separately from the SQL command.
364
+
</para>
365
+
<para>
366
+
Parameter binding can only be used for data. Other dynamic parts of the
367
+
SQL query must be filtered against a known list of allowed values.
368
+
</para>
369
+
<para>
370
+
<example>
371
+
<title>Avoiding SQL injection by using PDO prepared statements</title>
372
+
<programlisting role="php">
443
373
<![CDATA[
444
374
<?php
445
375

446
-
settype($offset, 'integer');
447
-
$query = "SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET $offset;";
448
-

449
-
// please note %d in the format string, using %s would be meaningless
450
-
$query = sprintf("SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET %d;",
451
-
$offset);
376
+
// The dynamic SQL part is validated against expected values
377
+
$sortingOrder = $_GET['sortingOrder'] === 'DESC' ? 'DESC' : 'ASC';
378
+
$productId = $_GET['productId'];
379
+
// The SQL is prepared with a placeholder
380
+
$stmt = $pdo->prepare("SELECT * FROM products WHERE id LIKE ? ORDER BY price {$sortingOrder}");
381
+
// The value is provided with LIKE wildcards
382
+
$stmt->execute(["%{$productId}%"]);
452
383

453
384
?>
454
385
]]>
455
-
</programlisting>
456
-
</example>
457
-
</para>
458
-
</listitem>
459
-
<listitem>
460
-
<simpara>
461
-
If the database layer doesn't support binding variables then
462
-
quote each non numeric user supplied value that is passed to the
463
-
database with the database-specific string escape function (e.g.
464
-
<function>mysql_real_escape_string</function>,
465
-
<function>sqlite_escape_string</function>, etc.).
466
-
Generic functions like <function>addslashes</function> are useful only
467
-
in a very specific environment (e.g. MySQL in a single-byte character
468
-
set with disabled <varname>NO_BACKSLASH_ESCAPES</varname>) so it is
469
-
better to avoid them.
470
-
</simpara>
471
-
</listitem>
472
-
<listitem>
473
-
<simpara>
474
-
Do not print out any database specific information, especially
475
-
about the schema, by fair means or foul. See also <link
476
-
linkend="security.errors">Error Reporting</link> and <link
477
-
linkend="ref.errorfunc">Error Handling and Logging Functions</link>.
478
-
</simpara>
479
-
</listitem>
480
-
<listitem>
481
-
<simpara>
482
-
You may use stored procedures and previously defined cursors to abstract
483
-
data access so that users do not directly access tables or views, but
484
-
this solution has another impacts.
485
-
</simpara>
486
-
</listitem>
487
-
</itemizedlist>
386
+
</programlisting>
387
+
</example>
388
+
</para>
389
+

390
+
<simpara>
391
+
Prepared statements are provided
392
+
<link linkend="pdo.prepared-statements">by PDO</link>,
393
+
<link linkend="mysqli.quickstart.prepared-statements">by MySQLi</link>,
394
+
and by other database libraries.
395
+
</simpara>
396
+

397
+
<simpara>
398
+
SQL injection attacks are mainly based on exploiting the code not being written
399
+
with security in mind. Never trust any input, especially
400
+
from the client side, even though it comes from a select box,
401
+
a hidden input field, or a cookie. The first example shows that such a
402
+
simple query can cause disasters.
403
+
</simpara>
404
+

405
+
<para>
406
+
A defense-in-depth strategy involves several good coding practices:
407
+
<itemizedlist>
408
+
<listitem>
409
+
<simpara>
410
+
Never connect to the database as a superuser or as the database owner.
411
+
Use always customized users with minimal privileges.
412
+
</simpara>
413
+
</listitem>
414
+
<listitem>
415
+
<simpara>
416
+
Check if the given input has the expected data type. <acronym>PHP</acronym> has
417
+
a wide range of input validating functions, from the simplest ones
418
+
found in <link linkend="ref.var">Variable Functions</link> and
419
+
in <link linkend="ref.ctype">Character Type Functions</link>
420
+
(e.g. <function>is_numeric</function>, <function>ctype_digit</function>
421
+
respectively) and onwards to the
422
+
<link linkend="ref.pcre">Perl Compatible Regular Expressions</link>
423
+
support.
424
+
</simpara>
425
+
</listitem>
426
+
<listitem>
427
+
<simpara>
428
+
If the application expects numerical input, consider verifying data
429
+
with <function>ctype_digit</function>, silently change its type
430
+
using <function>settype</function>, or use its numeric representation
431
+
by <function>sprintf</function>.
432
+
</simpara>
433
+
</listitem>
434
+
<listitem>
435
+
<simpara>
436
+
If the database layer doesn't support binding variables then
437
+
quote each non-numeric user-supplied value that is passed to the
438
+
database with the database-specific string escape function (e.g.
439
+
<function>mysql_real_escape_string</function>,
440
+
<function>sqlite_escape_string</function>, etc.).
441
+
Generic functions like <function>addslashes</function> are useful only
442
+
in a very specific environment (e.g. MySQL in a single-byte character
443
+
set with disabled <varname>NO_BACKSLASH_ESCAPES</varname>), so it is
444
+
better to avoid them.
445
+
</simpara>
446
+
</listitem>
447
+
<listitem>
448
+
<simpara>
449
+
Do not print out any database-specific information, especially
450
+
about the schema, by fair means or foul. See also <link
451
+
linkend="security.errors">Error Reporting</link> and <link
452
+
linkend="ref.errorfunc">Error Handling and Logging Functions</link>.
453
+
</simpara>
454
+
</listitem>
455
+
</itemizedlist>
456
+
</para>
488
457

489
458
<simpara>
490
459
Besides these, you benefit from logging queries either within your script
491
460
or by the database itself, if it supports logging. Obviously, the logging is unable
492
461
to prevent any harmful attempt, but it can be helpful to trace back which
493
-
application has been circumvented. The log is not useful by itself, but
462
+
application has been circumvented. The log is not useful by itself but
494
463
through the information it contains. More detail is generally better than less.
495
464
</simpara>
496
465
</sect2>
497
466



Tools (Romanian Manual)