Hi and hello
Today we will test our knowledge from 2 previous parts (and add something new.) What will it be? We’ll be writing login script but in 2 ways. More about later in this article
So, what will we learn today?
- How to use
mysqli
both in procedural and object-oriented way - How to use some other database managements systems
- What’s SQL injection and how to defend our app against it
- What are prepared statements and how they are superior to other methods
Fair amount of knowledge - and we will write the same functionality 2 times
So, prepare yourself for a bit of reading I’d divide this into 2 articles - I’ll try to publish them at the same time
Login mechanism
Before we start writing - we should have some plan. How will our code even work?
I have some ideas - here they are
- First - save both
username
andpassword
to variables and sanitize them - Then execute a query that checks if passed
username
is in our table- If there is no user like that, redirect to the login page with message
Invalid username or password
- If there is such user - continue a script
- If there is no user like that, redirect to the login page with message
- Using
password_verify
function check if passwords match- If yes, set
isLoged
session variable totrue
and redirect to the secret page - If not, redirect to the login page with message
Invalid username or password
- If yes, set
Why do we display the same messages? Purely for security reasons That’s how we hide from attacker contents of our database - even though username is valid, potential hacker won’t see it, as both messages are the same
So, it’s high time to start coding
Form template
I won’t be spending here too much time - this will be simple form with 2 inputs and a submit button
<?php session_start(); ?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Log in</title>
</head>
<body>
<h1>Log in</h1>
<form action="login.php" method = "POST">
<input type="text" name = "username" placeholder="username"><br/><br/>
<input type="password" name="password" placeholder="password"><br/><br/>
<button type="submit">Log in</button>
<?php
if(isset($_SESSION['error'])){
echo $_SESSION['error'];
unset($_SESSION['error']);
}
?>
</form>
</body>
</html>
I’ve also added php code for displaying eventual errors - it works like this:
- Check if $_SESSION[‘error’] is set
- Display it
- Unset it - So it won’t be here after refreshing the page
I’ll skip styling - you can do it yourself and let’s now focus on actual login - but first
Ways of connecting to database from PHP
There are plenty of options - We have at least 3
- Via procedural interface designated for DBMS
- Via object-oriented interface made for DBMS
- Using PDO
Using DBMS’s interface
What do all of those geeky-sounding terms even it mean?
-
Procedural - uses functions (explained in this part )
-
Object-oriented - uses objects Right now, I won’t be diving into details of OOP (Object-oriented programming) - this is covered in part 14 and part 16
Right now, let’s simplify an object to this definition
Object - data structure containing both functions and variables
Let’s see the comparison - here with MySQL
That’s what the procedural version looks like
$mysqli = mysqli_connect("example.com", "user", "password", "database");
$result = mysqli_query($mysqli, "SELECT `username` FROM `users`");
$row = mysqli_fetch_assoc($result);
echo $row['users'];
As you see - first we call a function called mysqli_connect
and save its result to $mysqli
variable. It creates connection between our code and database
Then with mysqli_query
we execute our query and save its results to $result
After all, we use mysqli_fetch_assoc
to fetch one row of data from $result
and assign it to $row
See how in mysqli_query
we have to pass $mysqli
as parameter to the function - without it, god knows how do we want to connect to database
Now let’s see the same thing - but this time object-oriented
$mysqli = new mysqli("example.com", "user", "password", "database");
$result = $mysqli->query("SELECT `username` FROM `users`");
$row = $result->fetch_assoc();
echo $row['users'];
It’s doing absolutely the same thing
- Creates
mysqli
object inside$mysqli
- Runs query using function from the object - then saves result to
$result
- Fetches data from
$result
to the$row
array
But look, how while executing query it doesn’t take $mysqli
as parameter - why is that?
Because while creating $mysqli
object it “saves” data inside it, so when function is called it can take that data from the object itself - no need to pass additional parameters
->
calls a functions from inside the object - basic syntax looks like this
$object->function(params);
Both forms are valid - use whichever one you prefer I find object-oriented way better than procedural, so I’ll write in this style
With this said - let’s write our login script
Writing login script
Before we even start with writing database connection itself - we have to do 2 more things
- Check if we got the request
- Save values from
$_POST
to variables - Validate and sanitize them
If you’re not familliar with those concepts, check out parts 7 and 8 and then come back here
Also, start a session - if you know nothing about them go back to part 5 of this course
<?php
session_start();
if(empty($_POST['username'])){
$_SESSION['error'] = "No username specified";
header("Location: index.php");
die();
}
if(empty($_POST['password'])){
$_SESSION['error'] = "No password specified";
header("Location: index.php");
die();
}
$username_s = htmlentities($_POST['username'], ENT_QUOTES, "UTF-8");
$password_s = htmlentities($_POST['password'], ENT_QUOTES, "UTF-8");
empty()
function returns true
if passed variable is empty - and we don’t want empty username or password, do we?
With this said - time for database connection
$db = new mysqli("localhost", "root", "", "firstproject");
As we see, creating new mysqli
object takes 4 arguments
- Host - where is your database located (URL/Address)
- Username - database username. If you installed MySQL and PHPMyAdmin manually, these credentials will differ - so you need to put your PHPMyAdmin user and password
- Pasword - password for database user - in XAMPP
root
has no password - Database name - here it’s
firstproject
because that’s how we named our table here
Is everything working as should? If not, just show the error, and terminate execution
if($db->connect_errno != 0){
$_SESSION['error'] = "Error in database connection";
header("Location: index.php");
die();
}
(Personally, I love this name of the last function. It ideally pictures what it does - terminate further code execution)
First, let’s maybe check the if username even exists - so we’ll write a query
$sql = "SELECT * FROM `users` WHERE `username` = ?";
Why do we have that ?
there? Because we will be using prepared statements. And it indicates, where to place passed values
That’s the safest and the best way of executing SQL queries in PHP It secures our code from possible attacks such as SQLi. But what’s that?
What is SQL Injection (SQLi)
To make it as short as possible - SQL injection that allows user to change SQL queries and in consequence gain access to vulnerable data (Like personal identifiable information - PII, or passwords)
It occurs when PHP treats user-passed data as valid SQL. So when?
Let’s say an attacker tries to compromise our website, so instead of normal username sends ' OR 1=1--
and password consists of some random characters
Then, if we added it to query like this
$sql = "SELECT * FROM `users` WHERE `username` ='".$username."'";
We end up with this query
SELECT * FROM `users` WHERE `username` ='' OR 1=1 --'
It’s valid SQL syntax and always returns true
, so we will gain access to the first account in database - and that’s often admin
With properly implemented authentication this should not happen, but this is very dangerous vulnerability, especially in places that return data to the users (like showing tasks on our website)
That’s it, from cybersec point of view - if you want to learn more, check out this module from Portswigger and let’s come back to coding right now
Prepared statements - executing query
First - create a statement
$stmt = $db->prepare($sql);
Then, pass the username and execute the query
$stmt->bind_param("s", $username_s);
$stmt->execute();
using bind_param()
function we substitute that question mark with value from $username_s
First parameter is a string of types - here I passed s
, as we have only one parameter with type string
. But there are also numerical types
i
- intd
- double
Example from the docs shows it perfectly
$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param('sssd', $code, $language, $official, $percent);
$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;
$stmt->execute();
$code
is string, $language
and $official
too, and $percent
is double -> string, string, string, double -> sssd
Coming back, We have to check if such user exists and get results of that query
$result = $stmt->get_result();
if($result->num_rows < 1){
$_SESSION['error'] = "invalid username or password ";
header("Location: index.php");
die();
}
$row = $result->fetch_assoc();
num_rows
is a variable inside $result
that stores integer representing how many rows were returned
In $row
we have beautiful associative array with id, username and password
Validation
Now we need only to validate if the password matches the hash
if(!password_verify($password_s, $row['password'])){
$_SESSION['error'] = "invalid username or password ";
header("Location: index.php");
die();
}
If it does, close the connection, set isLoged
variable and redirect to the secret page
$db->close();
$_SESSION['isLoged'] = true;
header("Location: secret.php");
With this variable we’ll ensure, that only authenticated users will have access to secret page
Create secret.php
and inside it check if isLoged
is set
<?php
session_start();
if(!isset($_SESSION['isLoged'])){
header("Location: index.php");
}
?>
Then, I created a simple HTML webpage
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
</head>
<body>
Welcome - you have successfully logged in
</body>
</html>
And that’s it - your login script is done
Conclusion
That’s the first half of this article - As I said before, I decided to split it into 2 parts, because
- One part would be too lengthy
- This would be too much new knowledge for one article
Second half will be posted soon I hope you enjoyed this article, and learned something new
Code from this part is available on my github and…
That’s it - check out my other articles too and see you in next ones