docs/sql.md

   1 `sqlauth:SQL`
   2 =============
   3
   4 This is a authentication module for authenticating a user against a SQL database.
   5
   6
   7 Options
   8 -------
   9
  10 `dsn`
  11 :   The DSN which should be used to connect to the database server.
  12     Check the various database drivers in the [PHP documentation](http://php.net/manual/en/pdo.drivers.php) for a description of the various DSN formats.
  13
  14 `username`
  15 :   The username which should be used when connecting to the database server.
  16
  17
  18 `password`
  19 :   The password which should be used when connecting to the database server.
  20
  21 `query`
  22 :   The SQL query which should be used to retrieve the user.
  23     The parameters :username and :password are available.
  24     If the username/password is incorrect, the query should return no rows.
  25     The name of the columns in resultset will be used as attribute names.
  26     If the query returns multiple rows, they will be merged into the attributes.
  27     Duplicate values and NULL values will be removed.
  28
  29
  30 Examples
  31 --------
  32
  33 Database layout used in some of the examples:
  34
  35     CREATE TABLE users (
  36       uid VARCHAR(30) NOT NULL PRIMARY KEY,
  37       password TEXT NOT NULL,
  38       salt TEXT NOT NULL,
  39       givenName TEXT NOT NULL,
  40       email TEXT NOT NULL,
  41       eduPersonPrincipalName TEXT NOT NULL
  42     );
  43     CREATE TABLE usergroups (
  44       uid VARCHAR(30) NOT NULL REFERENCES users (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  45       groupname VARCHAR(30) NOT NULL,
  46       UNIQUE(uid, groupname)
  47     );
  48
  49 Example query - SHA256 of salt + password, with the salt stored in an independent column, MySQL server:
  50
  51     SELECT uid, givenName, email, eduPersonPrincipalName
  52     FROM users
  53     WHERE uid = :username
  54     AND PASSWORD = SHA2(
  55         CONCAT(
  56             (SELECT salt FROM users WHERE uid = :username),
  57             :password
  58         ),
  59         256
  60     )
  61
  62 Example query - SHA256 of salt + password, with the salt stored in an independent column. Multiple groups, MySQL server:
  63
  64     SELECT users.uid, givenName, email, eduPersonPrincipalName, groupname AS groups
  65     FROM users LEFT JOIN usergroups ON users.uid = usergroups.username
  66     WHERE users.uid = :username
  67     AND PASSWORD = SHA2(
  68         CONCAT(
  69             (SELECT salt FROM users WHERE uid = :username),
  70             :password
  71         ),
  72         256
  73     )
  74
  75 Example query - SHA512 of salt + password, stored as salt (32 bytes) + sha256(salt + password) in password-field, PostgreSQL server:
  76
  77     SELECT uid, givenName, email, eduPersonPrincipalName
  78     FROM users
  79     WHERE username = :username
  80     AND SUBSTRING(
  81         password FROM LENGTH(password) - 31
  82     ) = SHA2(
  83         CONCAT(
  84             SUBSTRING(password FROM 1 FOR LENGTH(password) - 32),
  85             :password
  86         ),
  87         512
  88     )
  89
  90 Security considerations
  91 -----------------------
  92
  93 Please never store passwords in plaintext in a database. You should always hash your passwords with a secure one-way
  94 function like the ones in the SHA2 family. Use randomly generated salts with a length at least equal to the hash of the
  95 password itself. Salts should be per-password, that meaning every time a password changes, the salt must change, and
  96 therefore salts must be stored in the database alongside the passwords they were used for. Application-wide salts can
  97 be used (by just concatenating them to the input of the hash function), but should never replace per-password salts,
  98 used instead as an additional security measure.
  99
 100 One way hashing algorithms like MD5 or SHA1 are considered insecure and should therefore be avoided.