PHP development

Lesson 12

SQL language

https://www.w3schools.com/sql/default.asp

SQL is database query language meanig when you use the right syntax you can insert, update, retrieve and delete data from the database.

This comes in very handy when building and using a website. For instance if you want to log in on a website like digitalocean, you first need to create an account. You do so by filling in a registration form with your credentials like email address and password.
When you click the save button the server receives your login details and inserts these into the database.


INSERT INTO `users` 
    (`username`, `password`) 
VALUES 
    ('challajacob@gmail.com', 'mysecretpassword');

When you want to login, you have to insert these details in a login form. When you click the login button, the server receives your credentials. It then searches the database for your credentials. If matching credential are selected the server grants you access with a user id.


SELECT * FROM `users` 
WHERE 
    `username` = 'challajacob@gmail.com'
AND
    `password` = 'mysecretpassword';

When you want to change your password, you fill in an update form. after the server receives your new credentials, it selects your user data and updates those with the new password.


UPDATE `users` 
SET 
    `password` = 'mynewsecretpassword' 
WHERE 
    `username` = 'challajacob@gmail.com' 
AND `password` = 'mysecretpassword';

When you decide to leave digital ocean, you fill in a cancelation form. The server receives your cancelation data, selects your userdata and deletes it from the database.


DELETE * FROM `users` 
WHERE 
    `username` = 'challajacob@gmail.com' 
AND `password` = 'mynewsecretpassword';

Questions

Why is having a password like that in a database not a good idea?

How could we safe that password in a better way?

Homework

We have the following database:

Write a query that selects the full record while you only know the email address is challajacob@gmail.com.
write a function hashPassword() that takes a password Jqc08C4qq11a as argument. The return value (string) of the function should be a hashed password. Search php.net for a hashing function.


$password = 'Jqc08C4qq11a';

function hashPassword(string $password) : string
{
    //your code here   
}

echo encryptPassword($password);

write a function checkPassword() that takes a password and compares it with the hashed password.


$password1 = 'Jqc08C4qq11a';
$passwordInDatabase1 = '$2y$10$fWvzCiyjITtuBUaEWtYvkeXWcfECTSRjgGQtNrEib7DuAUoblEwOu';

$password2 = 'HelloWorld';
$passwordInDatabase2 = '$2y$10$ppM9zqrLZtisSPoYXjvBneayjQEL2cjYXw/meM3Y7tSLQh8q1J0Tu';

function checkPassword(string $password, string $hash) : bool
{
    //your code here
}

echo '
password1 = ' .checkPassword($password1, $passwordInDatabase1);

echo '
password2 = ' .checkPassword($password1, $passwordInDatabase1);