PHP 0 to hero pt.11 - Integrating SQL with PHP (SQL pt.3)
Tags: webdev, php, beginners, tutorial
Published: 2024-02-01 15:00:00

Hi and hello

Welcome in part 11 of this course - Today we will test our knowledge from 2 previous parts and add something new.
What would it be? We'll be writing login script but in 2 ways. More about later

So, what will we learn today?
1. How to use `mysqli` both in procedural and object-oriented way
2. How to use some other database managements systems
3. What is PDO - why and how to use it
4. What's SQL injection and how to defend our app against it
5. What are prepared statements and how they are superior to other methods

Kind a lot of it - and we will write the same functionality 2 times

So prepare yourself for a bit of reading
I'd divide this into 2 articles - both will be here and on my blog and 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
1. First - save both `username` and `password` to variables and sanitize them
2. 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 a user - continue a script
3. Using `password_verify` function check if passwords match
    - If yes, set `isLoged` session variable to `true` and redirect to the secret page
    - If not, redirect to the login page with message `Invalid username or password`


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 button

```php
<?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:
1. Check if $_SESSION['error'] is set
2. Display it
3. 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

So, there is no one way? 

No, there is no one way. We have at least 3

1. Via procedural interface of DBMS
2. Via object-oriented interface of DBMS
3. Using PDO

## Using DBMS's interface

What do all of those geeky-sounding terms even it mean? 
- **Procedural** means using **functions**

- **Object-oriented** means using **objects**
Right now, I won't be diving into details of OOP (Object-oriented programming) - this will be later

Right now, let's simplify object to this definition
> Object - data structure containing both functions and variables

Let's see the comparison - here with MySQL

That's how the procedural version looks like
```php
$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 it's result to `$mysqli` variable. It creates connection between our code and database
Then with `mysqli_query` we execute our query and save it's 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

Now let's see the same thing - but this time object-oriented

```php
$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
1. Creates `mysqli` object inside `$mysqli`
2. Runs query using function from the object - then saves result to `$result`
3. 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
```php
$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

So, before we even start with writing Database connection - we have to do 2 more things
1. Check if we got the request
2. Save values from `$_POST` to variables
3. Validate and sanitize them

You should be familiar with those, at this point - If not, check out parts [7](https://wizarddos.github.io/blog/programming/PHP_0_to_hero/7) and [8](https://wizarddos.github.io/blog/programming/PHP_0_to_hero/8) and then come back here

Also, start a session - if you know nothing about them check out [part 5](https://wizarddos.github.io/blog/programming/PHP_0_to_hero/5) of this course

```php
<?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");
```

Also `empty()` function returns `true` if passed variable is empty - and we don't want empty username or password do we?

With this said - let's start with database connection

```php
$db = new mysqli("localhost", "root", "", "firstproject");
```

As we see, creating new `mysqli` object takes 4 arguments
1. Host - where is your database located (URL/Address)
2. Username - database username. If you installed MySQL and PHPMyAdmin manually, these credentials will differ - so you need to put your valid ones
3. Pasword - password for database user - in XAMPP `root` has no password
4. Database name - here it's `firstproject` because that's how we named our table 2 parts ago

Is everything working? If not, just show the error, and terminate execution
```php
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 let's write the query
```php
$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
```php
$sql = "SELECT * FROM `users` WHERE `username` ='".$username."'";
```

We end up with this query
```sql
SELECT * FROM `users` WHERE `username` ='' OR 1=1 --'
```

It always returns `true`, so we will gain access to the first account in database - and that's often admin account

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](https://portswigger.net/web-security/sql-injection) and let's come back to writing

### Prepared statements - executing query

First - create a statement
```php
$stmt = $db->prepare($sql);
```

Then, pass the username and execute the query
```php
$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` - int
- `d` - double

Example [from the docs](https://www.php.net/manual/en/mysqli-stmt.bind-param.php) shows it perfectly
```php

$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();
```

We have to check if we got something and get results of that query

```php
$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 much rows were returned

Now 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

```php
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
```php
$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
<?php 
session_start(); 
if(!isset($_SESSION['isLoged'])){
    header("Location: index.php");
}
?>
```

Then, I created a simple HTML webpage
```php
<!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 - you've created a login script.

## Conclusion

That's the first half of this article - As I said before, I decided to split it into  2 parts, because 
1. One part would be too long
2. This would be too much new knowledge for one article

Second half will be posted soon - both here and on my blog
I hope you enjoyed this article, and learned something new

Code from this part is available on [my github](https://github.com/wizarddos/Dev.to-scripts/tree/master/PHP%200%20to%20hero/Part%2011%20-%20SQL%20pt.%203) and 

By the way, If you want to have earlier access to my new articles, follow [my blog](https://wizarddos.github.io/blog/)

That's it - check out my other articles too and see you in next ones