SQL date format convert? [dd.mm.yy to YYYY-MM-DD]

Since your input is a string in the form 03.09.13, I'll assume (since today is September 3, 2013) that it's dd-mm-yy. You can convert it to a date using STR_TO_DATE:

STR_TO_DATE(myVal, '%d.%m.%y')

Then you can format it back to a string using DATE_FORMAT:

DATE_FORMAT(STR_TO_DATE(myVal, '%d.%m.%y'), '%Y-%m-%d')

Note that the year is %y (lowercase "y") in STR_TO_DATE and %Y (uppercase "Y") in DATE_FORMAT. The lowercase version is for two-digit years and the uppercase is for four-digit years.

What is SQL injection and how to prevent it?

When working with databases, one of the most common security vulnerabilities in web applications is definitely SQL injection attack. Malicious users can insert SQL query into the input data you’re using in your SQL queries and instead unwanted behavior happens.

SQL injection example with PDO

// GET data is sent through URL: http://example.com/get-user.php?id=1 OR id=2;
$id = $_GET['id'] ?? null;

// You are executing your application as usual
// Connect to a database
$dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', 'dbusername', 'dbpassword');

// Select user based on the above ID
// bump! Here SQL code GET data gets injected in your query. Be careful to avoid
// such coding and use prepared statements instead
$sql = "SELECT username, email FROM users WHERE id = " . $id;

foreach ($dbh->query($sql) as $row) {
    printf ("%s (%s)\n", $row['username'], $row['email']);
}

Just imagine worst case scenarios with injected SQL:

"'DELETE FROM users */"

How to avoid SQL injection in above example? Use prepared statements:

$sql = "SELECT username, email FROM users WHERE id = :id";

$sth = $dbh->prepare($sql, [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY]);
$sth->execute([':id' => $id]);
$users = $sth->fetchAll();

mysqli example

When using MySQL database quite you can also use mysqli with prepared statements, or mysqli_real_escape_string() function, however you can just use more advanced PDO.

// get data is sent through url for example, http://example.com/get-user.php?id=1 OR id=2;
$id = $_GET['id'] ?? null;

// in your code you are executing your application as usual
$mysqli = new mysqli('localhost', 'db_user', 'db_password', 'db_name');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// bump! sql injected code gets inserted here. Be careful to avoid such coding
// and use prepared statements instead
$query = "SELECT username, email FROM users WHERE id = " . $id;

if ($result = $mysqli->query($query)) {
    // fetch object array
    while ($row = $result->fetch_row()) {
        printf ("%s (%s)\n", $row[0], $row[1]);
    }

    // free result set
    $result->close();
} else {
    die($mysqli->error);
}

Let’s fix this with prepared statements. They are more convenient because mysqli_real_escape_string() doesn’t apply quotes (it only escapes it).

// get data is sent through url for example, http://example.com/get-user.php?id=1 OR id=2;
$id = $_GET['id'] ?? null;

// in your code you are executing your application as usual
$mysqli = new mysqli('localhost', 'db_user', 'db_password', 'db_name');

if ($mysqli->connect_error) {
    die('Connect Error (' . $mysqli->connect_errno . ') ' . $mysqli->connect_error);
}

// bump! sql injected code gets inserted here. Be careful to avoid such coding
// and use prepared statements instead
$query = "SELECT username, email FROM users WHERE id = ?";

$stmt = $mysqli->stmt_init();

if ($stmt->prepare($query)) {
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_array(MYSQLI_NUM)) {
        printf ("%s (%s)\n", $row[0], $row[1]);
    }
}

See also

Other useful reading to check out:

• OWASP
• SQL injection - a community paradoxon
• Bobby Tables - A guide to preventing SQL injection.

How to Work With Users' Passwords and How to Securely Hash Passwords in PHP?

When you must save user’s password in a database you should never ever store them in plain text because of security precautions and privacy protection. Database where users’ passwords are stored might get compromised and by hashing them at least there is another safety mechanism for not revealing them to the attacker.

// plain text password example
$password = 'secretcode';

Cryptography is a large and quite complex field for a lot of people so a good rule of a thumb would be to leave it to the experts.

One of the most used but wrong way of hashing password was once using md5() which calculates md5 hash of a string. Hashing passwords with md5 (or sha1 or even sha256) is note safe anymore because these hashes can get decrypted very fast.

// plain text password
$password = 'secretcode';

// hash the password with md5
$md5 = md5($password);

Common solution to preventing decryption is using the salt.

// plain text password
$password = 'secretcode';

// add random number of random characters - the salt
$salt = '3x%%$bf83#dls2qgdf';

// hash salt and password together
$md5 = md5($salt.$password);

This is still not good enough though - Rainbow tables.

Right Way of Hashing Passwords in PHP

Right way of hashing passwords is currently using latest PHP version and its native passwords hashing API which provides an easy to use wrapper around crypt function.

Example of PHP native password hashing API usage:

// plain text password
$password = 'secretcode';

$options = ['cost' => 12];
echo password_hash($password, PASSWORD_DEFAULT, $options);

In password_hash() function there are currently two types of algorithm options available. PASSWORD_DEFAULT and PASSWORD_BCRYPT. Currently PASSWORD_DEFAULT is PASSWORD_BCRYPT and as language and cryptography progress there will be different types of algorithms supported. PASSWORD_DEFAULT will get replaced with that new type of algorithm (for example, Argon2). Good choice is to always use the PASSWORD_DEFAULT.

The database password’s field type should be varchar(255) for future proof algorithm changes.

Using your own salt is not a very good option. Leave that to the experts as well and use above bullet proof solution without setting your own salt. Salt is randomly generated by default in password_hash() function.

Another option that is important to mention is the cost which controls the hash speed. On servers with better resources cost can be increased. There is a script for calculating the cost for your environment in the PHP manual. As a good security practice try increasing this to higher value than the default 10.

Verifying passwords can be done with password_verify():

// this is the hash of the password in above example
$hash = '$2y$12$VD3vCfuHcxU0zcgDvArQSOlQmPv3tXW0TWoteV4QvBYL66khev0oq';

if (password_verify('secretcode', $hash)) {
    echo 'Password is valid!';
} else {
    echo 'Invalid password.';
}

Another useful function is password_needs_rehash() - which checks if given hash matches given options. This comes handy in case of server hardware upgrade and therefore increasing the cost option.

The hash string returned by password_hash() consists of the following parts:

$algorithm = substr($hash, 0, 4);  // $2y$ == BLOWFISH
$cost      = substr($hash, 4, 2);  // the cost
$salt      = substr($hash, 7, 22); // salt is automatically generated by default

Password Hashing in Older PHP Versions (5.5 and Below)

In case you’re still using some older PHP version, there is a way to secure passwords securely. Since PHP version > 5.3.7 you can use PHP library password_compat. PHP library password_compat works exactly the same way as does the native PHP password hashing API, so when you upgrade to latest PHP you will not need to refactor your code.

For PHP version below 5.3.6 phpass might be a good solution, but try to avoid these and use the native password hashing API.

Password Hashing in Open Source Projects

Some of the widely used PHP open source projects use different hashing algorithms for passwords because they either support older PHP versions where password_hash() wasn’t available yet or they already use the latest security recommendations by PHP security experts:

Project	Password hashing
CMS Airship	Argon2i
Drupal	SHA512Crypt with multiple rounds
Joomla	bcrypt
Laravel	bcrypt with other options
Symfony	bcrypt with other options
Wordpress	salted MD5

See Also

• PHP.net passwords FAQ
• csiphp.com - Interesting blog post about encrypting passwords
• password-validator - PHP library that validates passwords against PHP’s password_hash function using PASSWORD_DEFAULT. Will rehash when needed, and will upgrade legacy passwords with the Upgrade decorator.
• securepasswords.info - A polyglot repo of examples for using secure passwords (typically bcrypt).
• How to Safely Store Your Users’ Passwords in 2016