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?

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

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:

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

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:
ProjectPassword hashing
CMS AirshipArgon2i
DrupalSHA512Crypt with multiple rounds
Joomlabcrypt
Laravelbcrypt with other options
Symfonybcrypt with other options
Wordpresssalted MD5

See Also

Why is PHP Using Dollar Sign $ to Prefix Variables?

Rasmus Lerdorf - the father of the PHP language, explains the $ sign as an ability to insert variables inside literal string values (interpolation), so the variables are distinguished from the rest of the string. Dollar sign in front of the variable has been inspired by Perl which influenced PHP a lot in the beginning.
Also many other programming languages use the dollar character in their syntax. This symbol is called Sigil and simplifies interpolation among others.
Names not prefixed by $ are considered constants, functions, class names…
Sigil usage simplifies the variable interpolation into strings:


$name = "World";
echo "Hello, $name";
Where as in languages without sigil usage (for example, Python), you must either concatenate strings:
name = "World"
print "Hello, " + name
or use special interpolation syntax if the language provides it. For example, Ruby:
name = "World"
puts "Hello #{name}"
Many people used to other languages might find the sigil usage odd, but you can get used to it in no time and discover the benefits of this.

See Also

PHP basics

  1. PHP in a nutshell
  2. Basic syntax
    1. Hello world
    2. Operators
    3. Declarations
    4. Functions
    5. Control structures
    6. Arrays
    7. Built in types
  3. Errors
    1. PHP Errors
    2. What is next?
This tutorial will show you basic PHP syntax and its features. If you’re new to PHP read the most basic PHP tutorial to get started as quickly and painlessly as possible. For becoming modern PHP developer read and follow also PHP: The Right Way. Seriously.

PHP in a nutshell

  • scripting language
  • procedural and object oriented language
  • dynamically (weakly) typed
  • syntax similar to C, C++, C#, Java and Perl
  • Imperative language
  • PHP has closures

Basic syntax

Hello world

File hello.php:


echo 'Hello, world.';
$ php hello.php

Operators

Arithmetic operators

OperatorNameResult
-$anegationOpposite of $a.
$a + $badditionSum of $a and $b.
$a - $bSubtractionDifference of $a and $b.
$a * $bMultiplicationProduct of $a and $b.
$a / $bdivisionQuotient of $a and $b.
$a % $bmodulusRemainder of $a divided by $b.
$a ** $bExponentiationResult of raising $a to the $b’th power.

Comparison operators

OperatorNameResult
$a == $bEqualTRUE if $a is equal to $b after type juggling.
$a === $bIdenticalTRUE if $a is equal to $b, and they are of the same type.
$a != $bNot equalTRUE if $a is not equal to $b after type juggling.
$a <> $bNot equalTRUE if $a is not equal to $b after type juggling.
$a !== $bNot identicalTRUE if $a is not equal to $b, or they are not of the same type.
$a < $bLess thanTRUE if $a is strictly less than $b.
$a > $bGreater thanTRUE if $a is strictly greater than $b.
$a <= $bLess than or equal toTRUE if $a is less than or equal to $b.
$a >= $bGreater than or equal toTRUE if $a is greater than or equal to $b.

Logical operators

OperatorNameResult
$a and $bAndTRUE if both $a and $b are TRUE.
$a or $bOrTRUE if either $a or $b is TRUE.
$a xor $bXorTRUE if either $a or $b is TRUE, but not both.
! $aNotTRUE if $a is not TRUE.
$a && $bAndTRUE if both $a and $b are TRUE.
$a || $bOrTRUE if either $a or $b is TRUE.

Assignment operators

OperatorDescription
=Set a value to variable
+=Addition of numeric value to variable
.=Add string to variable

Declarations

  • $i = 1; - assign value to variable
  • define('FOO', 'something'); - define a constant *

Functions

// a simple function
function functionName() {}

// function with parameters
function functionName($param1, $param2) {}

Anonymous functions (closures)


echo preg_replace_callback('~-([a-z])~', function ($match) {
    return strtoupper($match[1]);
}, 'hello-world');
// outputs helloWorld

// Anonymous function variable assignment example
$greet = function($name) {
    printf("Hello %s\r\n", $name);
};
$greet('World');

// inherit
$message = 'hello';

// Without "use" keyword
$example = function () {
    var_dump($message);
};
echo $example();

// Inherit $message
$example = function () use ($message) {
    var_dump($message);
};
echo $example();

Variadic functions


function sum(...$nums)
{
    return array_sum($nums);
}

Control structures

If

if $x > 0 {
    return $x;
} else {
    return -$x;
}

Loops

// for
for ($i = 1; $i<10 span="">; $i++) {}

// while
while ($i < 10) {}

// do while
$i = 0;
do {
    echo $i;
} while ($i > 0);


// foreach
foreach ($array as $key => $value) {}

Switch

// switch statement
switch ($operatingSystem) {
    case 'darwin':
        echo 'Mac OS Hipster';
        break;
    case 'linux':
        echo 'Linux Geek';
        break;
    default:
        // Windows, BSD, ...
        echo 'Other';
}

Arrays

$array = [
    "foo" => "bar",
    "bar" => "foo",
];

Operations on arrays

OperatorNameResult
$a + $bUnionUnion of $a and $b.
$a == $bEqualityTRUE if $a and $b have the same key/value pairs.
$a === $bIdentityTRUE if $a and $b have the same key/value pairs in the same order and of the same types.
$a != $bInequalityTRUE if $a is not equal to $b.
$a <> $bInequalityTRUE if $a is not equal to $b.
$a !== $bNon-identityTRUE if $a is not identical to $b.

Built-in types

Type
boolean
integer
float
string
array
object
resource
NULL

Errors

PHP errors


// Turn off all error reporting
error_reporting(0);

// Report simple running errors
error_reporting(E_ERROR | E_WARNING | E_PARSE);

// Reporting E_NOTICE can be good too (to report uninitialized
// variables or catch variable name misspellings ...)
error_reporting(E_ERROR | E_WARNING | E_PARSE | E_NOTICE);

// Report all errors except E_NOTICE
error_reporting(E_ALL & ~E_NOTICE);

// Report all PHP errors (see changelog)
error_reporting(E_ALL);

// Report all PHP errors
error_reporting(-1);

// Same as error_reporting(E_ALL);
ini_set('error_reporting', E_ALL);

Exceptions

function inverse($x) {
    if (!$x) {
        throw new Exception('Division by zero.');
    }
    return 1/$x;
}

try {
    echo inverse(5) . "\n";
} catch (Exception $e) {
    echo 'Caught exception: ',  $e->getMessage(), "\n";
} finally {
    echo "This is always executed.\n";
}

What is next?

Use php.net. It has a great and detailed manual for a lot of your PHP adventures.