A Guide to Encryption and Hashing in Laravel

https://ashallendesign.ams3.cdn.digitaloceanspaces.com/public/blog/128/a-guide-to-encryption-and-hashing-in-laravel.png

Every web developer should be aware of hashing and encryption because they are vital security concepts. When used correctly, they can improve web application security and help ensure the privacy of users’ personally identifiable information (PII).

In this article, we’ll explore hashing and encryption, including their differences. We’ll also learn how to use them within Laravel projects to improve application security. We’ll cover how to hash data and compare it to plain-text values. We’ll also explain the different ways that you can encrypt and decrypt data in Laravel.

What Are Hashing and Encryption?

As a web developer, you’ve likely encountered the terms “encryption” and “hashing”. You may have even used them in your own projects, but what do they mean, and how are they different?

These terms are sometimes used interchangeably, but they are different techniques with their own use cases.

Encryption and hashing are both forms of “cryptographic security” and consist of using mathematical algorithms to transform data into a form that is unreadable to humans and (mostly) secure.

The world of cryptography is complex and relies heavily on mathematics. Therefore, for the sake of this article, we’ll keep things simple and focus on the high-level concepts of both techniques and how they can be used in your Laravel applications.

Encryption

Let’s start by taking a look at encryption.

Encryption is the process of encoding information (usually referred to as “plaintext”) into an alternative form (usually referred to as “ciphertext”). Typically, ciphertext is generated using a “key” and an encryption algorithm. It can then only be decrypted and read by someone who has the correct key, depending on the type of encryption used.

Encryption can come in two forms: “symmetric” and “asymmetric”.

Symmetric encryption uses the same key for both encryption and decryption. This means that the same key is used to encrypt and decrypt the data. Examples of this type of encryption include Advanced Encryption Standard (AES) and Data Encryption Standard (DES). Laravel uses AES-256-CBC encryption by default.

Asymmetric encryption is a bit more complicated and uses a pair of keys: a public key and a private key. The public key is used to encrypt the data, and the private key is used to decrypt the data. Thus, the public key can be shared with anyone, but the private key must be kept secret, as anyone with it can successfully decrypt the data. Examples of this type of encryption include Rivest–Shamir–Adleman (RSA) and Diffie-Hellman.

As an example of what an encrypted string might look like, let’s say that we have a string of text that we want to encrypt. We’ll use the following string as an example:

        

1This is a secret message.

If we encrypted this text using AES-256-CBC encryption and used U2x2QdvosFTtk5nL0ejrKqLFP1tUDtSt as the key, we would get the following ciphertext:

        

1eyJpdiI6IjF1cmF0YU5TMkRnR3NUMVRMMm1udFE9PSIsInZhbHVlIjoieGloYW5VVWtXV2hjcVRVY3hGTkZ1bDdoOVBSZEo1VkVWNE1LSlB5S0lhNkF3SloxeWhRejNwbjN5SEgxeUJXayIsIm1hYyI6IjljNzY0MTBmMGJlZmRjNzcwMjFiMmFjYmJhNTNkNWVhODkxMTgzYmYwMjA3N2YzMjM1YmVhZWU4NDRiOTYzZWQiLCJ0YWciOiIifQ==

If we were then to decrypt the above ciphertext using the same key, we would get the original plaintext:

        

1This is a secret message.

Hashing

Hashing is a bit different from encryption. It is a one-way process, meaning that when data is “hashed”, it’s transformed into what is referred to as a “hash value”. However, unlike encryption, you cannot reverse the process and get the original data. Hence, the hash value is not reversible.

Typically, you’d want to use a hash in a situation where you don’t want any data to be retrieved in the event of a leak. For example, you may want to store a user’s password in your database but wouldn’t want to store it in plain text in case the database was ever compromised. If you did store it in plain text, you would potentially be giving malicious hackers a list of passwords in use. In an ideal world, all users would use unique passwords, but we all know that this isn’t the case. Therefore, a leaked database of plain-text passwords would be a goldmine for hackers because they could then try to use the passwords on other websites or applications. To combat this problem, we store passwords in a hashed format.

For example, let’s say that a user’s password is “hello”. If we were to use the bcrypt algorithm to hash this password, it would look something like this:

        

1$2y$10$XY2DMYKvLrMj7yqYwyvK5OSvKTA6HOoPTpe3gVpVP5.Y4kN1nbOLq

An important part of hashing is the use of a “salt”. A salt is a random string of characters used to make the hashed values of the same data different. This means that if two users have the same password, their hashed passwords would be stored differently in the database. This is important because it would stop hackers from being able to deduce which users had the same password. For instance, the following strings are all hashed versions of hello:

        

1$2y$10$NUgYbLrzxn471GzcIN10wedXEcltcbAasHqU7hCeMFv4aCTl/6bVW

        

1$2y$10$AvBxO6HCRwYPNPZmeERIEOzLAJP7ZkcjrekdzaRLwY8YX4m9VJiFy

        

1$2y$10$YQ3lzNx8h0tDgw4K3dzJAOxycZhDhTAnueSugbmoo3NDTuq1OT8KW

You’re probably thinking, “If it’s hashed and can’t be reversed, how do we know if the password is correct?”. We can do this by comparing the hash value of the password that the user has entered with the hash value of the password that is stored in the database by using the password_verify() function PHP provides. If they match, then we know that the password is correct. If they don’t match, then we know that the password is incorrect. Later in this article, we’ll cover how you can do this comparison in Laravel.

Many hashing algorithms are available, such as bcrypt, argon2i, argon2id, md5, sha1, sha256, sha512, and many more. However, when dealing with passwords, you should always use a hashing algorithm designed to be slow, such as bcrypt, because it makes it more difficult for hackers to brute-force the passwords.

Hashing in Laravel

Now that we have a basic idea of what hashing is, let’s take a look at how hashing works within the Laravel framework.

Hashing Passwords

As mentioned previously, you don’t want to store users’ passwords as plain text in a database. Instead, you’ll want to store their passwords in a hashed format. This is where the Hash facade comes into play.

By default, Laravel supports three different hashing algorithms: “Bcrypt”, “Argon2i”, and “Argon2id”. By default, Laravel uses the “Bcrypt” algorithm, but if you want to use a different one, Laravel allows you to change it. We’ll cover this topic in more detail later in the article.

To get started with hashing a value, you can use the Hash facade. For example, let’s say that we want to hash the password “hello”:

        

1use Illuminate\Support\Facades\Hash;

2 

3$hashedValue = Hash::make('hello');

4 

5// $hashedValue = $2y$10$XY2DMYKvLrMj7yqYwyvK5OSvKTA6HOoPTpe3gVpVP5.Y4kN1nbOLq

As you can see, it’s really easy to hash a value.

To give this a bit of context, let’s look at how it might work in a controller in your application. Imagine that you have a PasswordController that allows authenticated users to update their password. The controller may look something like so:

        

1namespace App\Http\Controllers;

2 

3use App\Http\Controllers\Controller;

4use Illuminate\Http\Request;

5use Illuminate\Support\Facades\Hash;

6 

7final class PasswordController extends Controller

8{

9 public function update(Request $request)

10 {

11 // Validate the new password here...

12 

13 $request->user()->fill([

14 'password' => Hash::make($request->newPassword)

15 ])->save();

16 }

17}

Comparing Hashed Values to Plain Text

As previously mentioned, it’s not possible to reverse a hashed value. Therefore, if we want to determine the hashed value, we can only do so by verifying the hashed value against a plain-text value. This is where the Hash::check() method comes into play.

Let’s imagine that we want to determine whether the “hello” plain-text password is the same as the hashed password that we created earlier. We can do this by using the Hash::check() method:

        

1$plainTextPassword = 'hello';

2$hashedPassword = "$2y$10$XY2DMYKvLrMj7yqYwyvK5OSvKTA6HOoPTpe3gVpVP5.Y4kN1nbOLq";

3 

4if (Hash::check($plainTextPassword, $hashedPassword)) {

5 // The passwords match...

6} else {

7 // The passwords do not match...

8}

The Hash::check() method will return true if the plain-text password matches the hashed password. Otherwise, it will return false.

This type of approach is what would typically be used when a user is logging into your application. If we were to ignore any additional security measures (such as rate limiting) for our login form, your login flow might be following the steps below:

  • The user provides an email and password.
  • Attempt to retrieve a user from the database with the given email.
  • If a user is found, then compare the given password with the hashed password stored in the database by using the Hash::check method.
  • If the Hash::check method returns true, then the user has successfully logged in. Otherwise, they’ve entered the incorrect password.

Hash Drivers in Laravel

Laravel provides the functionality for you to choose between different hashing algorithms. By default, Laravel uses the “Bcrypt” algorithm, but you can change it to either the “Argon2i” or the “Argon2id” algorithm, which are also supported. Alternatively, you can implement your own hashing algorithm, but this is strongly discouraged because you may introduce security vulnerabilities into your application. Instead, you should use one of the algorithms provided through PHP so that you can be sure the algorithms are tried and tested.

To change the hashing algorithm being used across your entire application, you can change the driver value in the config/hashing.php config file. The default value is bcrypt, but you can change this to either argon or argon2id, like so:

        

1return [

2 

3 // ...

4 

5 'driver' => 'bcrypt',

6 

7 // ...

8 

9];

Alternatively, if you’d prefer to explicitly define the algorithm that should be used, you can use the driver method on Hash facade to determine which hashing driver is used. For example, if you wanted to use the “Argon2i” algorithm, you could do the following:

        

1$hashedValue = Hash::driver('argon')->make('hello');

Encryption in Laravel

Let’s now take a look at how to use encryption in Laravel.

Encrypting and Decrypting Values

To get started with encrypting values in Laravel, you can make use of the encrypt() and decrypt() helper functions or the Crypt facade, which provides the same functionality. For example, let’s say that we want to encrypt the string “hello”:

        

1$encryptedValue = encrypt('hello');

By running the above, the encryptedValue variable would now be equal to something like this:

        

1eyJpdiI6IitBcjVRanJTN3hTdnV6REdScVZFMFE9PSIsInZhbHVlIjoiZGcycC9pTmNKRjU3RWpmeW1GdFErdz09IiwibWFjIjoiODg2N2U0ZTQ1NDM3YjhhNTFjMjFmNmE4OTA2NDI0NzRhZmI2YTg5NzEwYjdmY2VlMjFhMGZhYzE5MGI2NDA3NCIsInRhZyI6IiJ9

Now that we have the encrypted value, we can decrypt it by using the decrypt() helper function:

        

1$encryptedValue = 'eyJpdiI6IitBcjVRanJTN3hTdnV6REdScVZFMFE9PSIsInZhbHVlIjoiZGcycC9pTmNKRjU3RWpmeW1GdFErdz09IiwibWFjIjoiODg2N2U0ZTQ1NDM3YjhhNTFjMjFmNmE4OTA2NDI0NzRhZmI2YTg5NzEwYjdmY2VlMjFhMGZhYzE5MGI2NDA3NCIsInRhZyI6IiJ9';

2 

3$decryptedValue = decrypt($encryptedValue);

By running the above, the decryptedValue variable would now be equal to "hello".

If any incorrect data were passed to the decrypt() helper function, an Illuminate\Contracts\Encryption\DecryptException exception would be thrown.

As you can see, encrypting and decrypting data in Laravel is relatively easy. It can simplify the process of storing sensitive data in your database and then decrypting it when you need to use it.

Changing the Encryption Key and Algorithm

As mentioned earlier in the article, Laravel uses the “AES-256” symmetric encryption algorithm by default, meaning that a single key is used for encrypting and decrypting data. By default, this key is the APP_KEY value defined in your .env file. This is very important to remember because if you change your application’s APP_KEY, then any encrypted data that you have stored can no longer be decrypted (without making changes to your code to explicitly use the older key).

If you wish to change the encryption key without changing your APP_KEY, you can do so by changing the key value in the config/app.php config file. Likewise, you can also change the cipher value to specify the encryption algorithm used. The default value is AES-256-CBC, but can be changed to AES-128-CBC, AES-128-GCM, or AES-256-GCM.

Automatically Encrypting Model Attributes

If your application is storing sensitive information in the database, such as API keys or PII, you can use make use of Laravel’s encrypted model cast. This model cast automatically encrypts data before storing it in the database and then decrypts it when it’s retrieved.

This is a useful feature because it allows you to continue working with your data as if it weren’t encrypted, but it’s stored in an encrypted format.

For example, to encrypt the my_secret_field on a User model, you could update your model like so:

        

1class User extends Model

2{

3 protected $casts = [

4 'my_secret_field' => 'encrypted',

5 ];

6}

Now that it’s defined as an accessor, you can continue using the field as usual. Thus, if we wanted to update the value stored in the my_secret_field, we could still use the update method like so:

        

1$user->update(['my_secret_field' => 'hello123']);

Notice how we didn’t need to encrypt the data before passing it to the update method.

If you were to now inspect the row in the database, you wouldn’t see "hello123" in the user’s my_secret_field field. Instead, you’d see an encrypted version of it, such as the following:

        

1eyJpdiI6IjM3MUxuV0lKc2RjSGNYT2dXanhKeXc9PSIsInZhbHVlIjoiNmxPZjUray9ZV21Ba1RnRkFNdHRTZz09IiwibWFjIjoiNTNlNmU0YTY5OGFjZWU2OGJiYzY4OWYzYzExYjMzNTI0MDQ2YTJiM2M4YWZkMjkyMGQxNmQ2MmYwNzQyNGFjYSIsInRhZyI6IiJ9

Thanks to the encrypted model cast, we would still be able to use the intended value of the field. For example,

        

1$result = $user->my_secret_field;

2 

3// $result is equal to: "hello123"

As you can imagine, using the encrypted model cast is a great way to quickly add encryption to an application without having to manually encrypt and decrypt the data. Hence, it is a quick way to improve data security.

However, it does have some limitations, of which you should be aware. First, because the encryption and decryption are run when storing and fetching the Model, calls using the DB facade won’t automatically do the same. Therefore, if you intend to make any queries to the database using the DB facade (rather than using something like Model::find() or Model::get()), you’ll need to manually handle the encryption.

Furthermore, it’s worth noting that although encrypting fields in the database improves security, it doesn’t mean the data are completely secure. If a malicious attacker finds the encryption key, they could decrypt the data and access the sensitive information. Therefore, encrypting the fields is beneficial only if the database is compromised. If you’re using a key (such as your application’s APP_KEY) that’s stored in your .env file, then a breach of your application server would also allow the attacker to decrypt the data.

Using a Custom Encryption Key When Manually Encrypting Data

When encrypting and decrypting data, there may be times when you want to use a custom encryption key. You might want to do this to avoid coupling your encrypted data to your application’s APP_KEY value. Alternatively, you may want to give users the ability to define their own encryption keys so that (theoretically) only they can decrypt their own data.

If you’re manually encrypting and decrypting data, to define your own encryption key, you can instantiate the \Illuminate\Encryption\Encrypter class and pass the key to the constructor. For example, let’s imagine that we want to encrypt some data using a different encryption key. Our code may look something like so:

        

1use Illuminate\Encryption\Encrypter;

2 

3// Our custom encryption key:

4$key = 'U2x2QdvosFTtk5nL0ejrKqLFP1tUDtSt';

5 

6$encrypter = new Encrypter(

7 key: $key,

8 cipher: config('app.cipher'),

9);

10 

11$encryptedValue = $encrypter->encrypt('hello');

As you can see, this is easy to do and adds the flexibility to use a custom encryption key. Now, if we were to try and decrypt the $encryptedValue variable, we would need to use the same key that we used to encrypt it and wouldn’t be able to run the decrypt() helper function because it wouldn’t be using the correct key.

Using a Custom Encryption Key When Using Model Casts

If you’re using the encrypted model cast, as we’ve covered earlier in this article, and you want to use a custom key for the encrypted fields, you can define your own encrypter for Laravel to use by using the Model::encryptUsing method.

We’d typically want to do this within a service provider (such as the App\Providers\AppServiceProvider) so that the custom encrypter is defined and ready to use when the application starts.

Let’s take a look at an example of how we could use the Model::encryptUsing method within our AppServiceProvider:

        

1declare(strict_types=1);

2 

3namespace App\Providers;

4 

5use Illuminate\Database\Eloquent\Model;

6use Illuminate\Encryption\Encrypter;

7 

8final class AppServiceProvider extends ServiceProvider

9{

10 public function boot(): void

11 {

12 $this->defineCustomModelEncrypter();

13 }

14 

15 private function defineCustomModelEncrypter(): void

16 {

17 // Our custom encryption key:

18 $key = 'U2x2QdvosFTtk5nL0ejrKqLFP1tUDtSt';

19 

20 $encrypter = new Encrypter(

21 key: $key,

22 cipher: config('app.cipher'),

23 );

24 

25 Model::encryptUsing($encrypter);

26 }

27 

28 // ...

29}

As you can see in the example above, defining the custom encrypter is very similar to how we defined it manually earlier in this article. The only difference is that we’re passing the Encrypter object to the Model::encryptUsing method so that Laravel can use it behind-the-scenes for us.

Encrypting Your .env File

As of Laravel 9.32.0, your application’s .env file can also be encrypted. This is useful if you want to store sensitive information in your .env file to your source control (such as git) but don’t want to store it in plain text. This is also beneficial because it allows a local versions of your .env config variables to be shared with other developers on your team for local development purposes.

To encrypt your environment file, you’ll need to run the following command in your project root:

This will generate an output similar to this:

        

1INFO Environment successfully encrypted.

2

3Key ........................ base64:amNvB/EvaX1xU+5R9Z37MeKR8gyeIRxh1Ku0pqNlK1Y

4Cipher ............................................................ AES-256-CBC

5Encrypted file ................................................. .env.encrypted

As we can see from the output, the command has used the key base64:amNvB/EvaX1xU+5R9Z37MeKR8gyeIRxh1Ku0pqNlK1Y to encrypt the .env file using the AES-256-CBC cipher and then stored the encrypted value in the .env.encrypted file.

This means that we can now store that file in our source control, and other developers on our team can use the same key to decrypt the file and use the values within it.

To decrypt the file, you need to run the php artisan env:decrypt command and pass the key used to encrypt the file. For example,

        

1php artisan env:decrypt base64:amNvB/EvaX1xU+5R9Z37MeKR8gyeIRxh1Ku0pqNlK1Y

This will then decrypt the .env.encrypted file and store the decrypted values in the .env file.

Conclusion

Hopefully, reading this article has given you a basic understanding hashing and encryption, as well as the differences between them. It should also have given you the confidence to use both concepts within Laravel projects to improve the security of your applications.

Laravel News Links

12 Laravel security best practices for 2023

https://res.cloudinary.com/benjamin-crozat/image/upload/dpr_auto,f_auto,q_auto,w_auto/v1690645860/dhjbi100_uiarfm.png

Don’t track your .env file

Your .env file contains sensitive information.

Please, don’t track it!

Make sure it’s included in your .gitignore.

Most of the time, data leaks are inside jobs.

A password manager is a better solution for sharing credentials.

If you want your team members to have access to a curated set of sensitive information, use a password manager with a proven track record of rock-solid security.

Keep Laravel up to date

Keeping Laravel up to date allows you to stay in touch with the latest security updates.

Make sure you are running a version of Laravel that is still being patched, and you should be OK.

If you never upgraded a Laravel project, I wrote a guide that will teach you how to do it.

Keep your first and third-party packages up to date

Access to dozens of packages from the official Laravel ecosystem and thousands of community packages is what makes our job easier.

But the more packages you use, the more points of failure you can be subject to.

Regularly running composer update goes a long way toward a more secure codebase.

composer update in action.

Disable debug messages in production

Make sure these two environment variables are correctly set in production.

APP_ENV=production

APP_DEBUG=false

You don’t want to leak information about your app’s architecture or configuration. Usually, debug messages contain a lot of this kind of details.

Don’t send sensitive information to error monitoring tools

Talking about sensitive information in debug messages, we haven’t eradicated them yet.

If you are using an error monitoring tool, you have to them there as well.

PHP 8.2 introduced a new attribute, \SensitiveParameter, that can hide anything from the stack trace (which is sent to error monitoring tools).

function something(

#[\SensitiveParameter]

$top_secret_parameter,

$who_cares_parameter,

) {

throw new \Exception('Whoops, something bad happended!');

}

Restrict parts of your app with policies

Policies in Laravel are like nightclub bouncers preventing people from accessing restricted areas.

Let’s look at a real-world example of using a Policy:

// app/Policies/PostPolicy.php

public function update(User $user, Post $post)

{

return $user->id === $post->user_id;

}

 

// app/Http/Controllers/PostController.php

public function update(Request $request, Post $post)

{

$this->authorize('update', $post);

 

// ...

}

As you can see, they make it really easy to check for permission before allowing a user to do anything.

Make sure to check the documentation, because there’s a lot to learn about Policies.

Protect your forms from cross-site request forgery (CSRF)

You might want to use the @csrf Blade directive in your forms.

<form method="POST" action="">

@csrf

 

<p>

<label for="name">First Name</label>

<input type="text" id="name" name="name" value="" required />

</p>

 

</form>

This directive generates a hidden input field containing a CSRF token automatically included when submitting the form.

This token confirms that the form is being submitted from your application and not by a third party.

The verification is handled by the VerifyCsrfToken middleware that Laravel uses by default for all your web routes.

Learn more about CSRF protection in Laravel’s documentation.

Validate the user’s input

Validation in Laravel is crucial in ensuring your application’s security.

Validation rules are numerous and will help you sanitize the data your users send with ease. Because you know the drill, right? Never trust your users’ input.

use Illuminate\Http\Request;

 

class PostController extends Controller

{

function store(Request $request)

{

$validated = $request->validate([

'user_id' => 'required|exists:users,id',

'title' => 'required|string|min:3|max:255',

'content' => 'required|string|min:3',

'published' => 'sometimes|boolean'

]);

 

Post::create($validated);

 

//

}

}

Learn more about validation in Laravel on the official documentation.

Be careful with uploaded files

As we saw, the user’s input must never be trusted. That also goes for the files they upload. Here are a few recommendations:

  1. Check the file’s MIME type (Laravel has the right validation rules for that).

$request->validate([

'file' => 'required|mimes:gif,jpeg,png,webp',

]);

  1. When possible, don’t make uploaded files publicly accessible (using the local file driver, for instance).

  2. Upload files on another server. If a hacker bypasses your securities, they won’t be able to run unauthorized code and access sensitive information.

  3. Delegate file uploads to a third-party service reputed for its security (meaning they never leaked data).

Encrypt the payload of your jobs

Whenever you dispatch a job, its payload is saved into the database, Redis or whatever else you told Laravel to use using the QUEUE_DRIVER environment variable.

The payload may contain sensitive information that any of your employee may look at and potentially misuse. As I said in the beginning of this article, leaks are often initiated by employees.

Fortunately, Laravel provides a the Illuminate\Contracts\Queue\ShouldBeEncrypted Contract, which will automatically encrypt the payloads. To make sure nobody can unencrypt them, make sure the Laravel’s APP_KEY defined in your .env is unaccessible to anyone besides a few trusted people.

use Illuminate\Contracts\Queue\ShouldQueue;

use Illuminate\Contracts\Queue\ShouldBeEncrypted;

 

class SomeJob implements ShouldQueue, ShouldBeEncrypted

{

//

}

Write tests for security risks

Testing is unfortunately a vast and lesser-known topic among developers.

Automatically testing multiple scenarii for potential security breaches is a great way to make sure they stay closed.

Laracasts provides free testing courses to help you get started. One with PHPUnit, the industry standard, and one with Pest, the best testing framework on this planet that modernizes and simplifies testing in PHP.

Keep your project tested

Do regular security audits

This practice is one of the most efficient and should be mandatory for anyone that is really serious about security. External feedback can be eyes opening.

As you can imagine, doing security audits isn’t free. It might only be worth it for enterprise since it would cost even more to pay for the potential fines! You cannot put a price on maintaining a good reputation and the trust of your users.

Laravel News Links

MySQL Capacity Planning

https://www.percona.com/blog/wp-content/uploads/2023/08/MySQL-capacity-planning-150×150.jpgMySQL capacity planning

As businesses grow and develop, the requirements that they have for their data platform grow along with it. As such, one of the more common questions I get from my clients is whether or not their system will be able to endure an anticipated load increase. Or worse yet, sometimes I get questions about regaining normal operations after a traffic increase caused performance destabilization.

As the subject of this blog post suggests, this all comes down to proper capacity planning. Unfortunately, this topic is more of an art than a science, given that there is really no foolproof algorithm or approach that can tell you exactly where you might hit a bottleneck with server performance. But we can discuss common bottlenecks, how to assess them, and have a better understanding as to why proactive monitoring is so important when it comes to responding to traffic growth.

Hardware considerations

The first thing we have to consider here is the resources that the underlying host provides to the database. Let’s take a look at each common resource. In each case, I’ll explain why a 2x increase in traffic doesn’t necessarily mean you’ll have a 2x increase in resource consumption.

Memory

Memory is one of the easier resources to predict and forecast and one of the few places where an algorithm might help you, but for this, we need to know a bit about how MySQL uses memory.

MySQL has two main memory consumers. Global caches like the InnoDB buffer pool and MyISAM key cache and session-level caches like the sort buffer, join buffer, random read buffer, etc.

Global memory caches are static in size as they are defined solely by the configuration of the database itself. What this means is that if you have a buffer pool set to 64Gb, having an increase in traffic isn’t going to make this any bigger or smaller. What changes is how session-level caches are allocated, which may result in larger memory consumption.

A tool that was popular at one time for calculating memory consumption was mysqlcalculator.com. Using this tool, you could enter in your values for your global and session variables and the number of max connections, and it would return the amount of memory that MySQL would consume. In practice, this calculation doesn’t really work, and that’s due to the fact that caches like the sort buffer and join buffer aren’t allocated when a new connection is made; they are only allocated when a query is run and only if MySQL determines that one or more of the session caches will be needed for that query. So idle connections don’t use much memory at all, and active connections may not use much more if they don’t require any of the session-level caches to complete their query.

The way I get around this is to estimate the amount of memory consumed on average by sessions as such…

({Total memory consumed by MySQL} – {sum of all global caches}) / {average number of active sessions}

Keep in mind that even this isn’t going to be super accurate, but at least it gives you an idea of what common session-level memory usage looks like. If you can figure out what the average memory consumption is per active session, then you can forecast what 2x the number of active sessions will consume.

This sounds simple enough, but in reality, there could be more to consider. Does your traffic increase come with updated code changes that change the queries? Do these queries use more caches? Will your increase in traffic mean more data, and if so, will you need to grow your global cache to ensure more data fits into it?

With the points above under consideration, we know that we can generally predict what MySQL will do with memory under a traffic increase, but there may be changes that could be unforeseen that could change the amount of memory that sessions use.

The solution is proactive monitoring using time-lapse metrics monitoring like what you would get with Percona Monitoring and Management (PMM). Keep an eye on your active session graph and your memory consumption graph and see how they relate to one another. Checking this frequently can help you get a better understanding of how session memory allocation changes over time and will give you a better understanding of what you might need as traffic increases.

CPU

When it comes to CPU, there’s obviously a large number of factors that contribute to usage. The most common is the queries that you run against MySQL itself. However, having a 2x increase in traffic may not lead to a 2x increase in CPU as, like memory, it really depends on the queries that are run against the database. In fact, the most common cause of massive CPU increase that I’ve seen isn’t traffic increase; it’s code changes that introduced inefficient revisions to existing queries or new queries. As such, a 0% increase in traffic can result in full CPU saturation.

This is where proactive monitoring comes into play again. Keep an eye on CPU graphs as traffic increases. In addition, you can collect full query profiles on a regular basis and run them through tools like pt-query-digest or look at the Query Analyzer (QAN) in PMM to keep track of query performance, noting where queries may be less performant than they once were, or when new queries have unexpected high load.

Disk space

A 2x increase in traffic doesn’t mean a 2x increase in disk space consumption. It may increase the rate at which disk space is accumulated, but that also depends on how much of the traffic increase is write-focused. If you have a 4x increase in reads and a 1.05X increase in writes, then you don’t need to be overly concerned about disk space consumption rates.

Once again, we look at proactive monitoring to help us. Using time-lapse metrics monitoring, we can monitor overall disk consumption and the rate at which consumption occurs and then predict how much time we have left before we run out of space.

Disk IOPS

The amount of disk IOPS your system uses will be somewhat related to how much of your data can fit into memory. Keep in mind that the disk will still need to be used for background operations as well, including writing to the InnoDB redo log, persisting/checkpointing data changes to table spaces from the redo log, etc. But, for example, if you have a large traffic increase that’s read-dependent and all of the data being read in the buffer pool, you may not see much of an IOPS increase at all.

Guess what we should do in this case? If you said “proactive monitoring,” you get a gold star. Keep an eye out for metrics related to IOPS and disk utilization as traffic increases.

Before we move on to the next section, consider the differences in abnormal between disk space and disk IOPS. When you saturate disk IOPS, your system is going to run slow. If you fill up your disk, your database will start throwing errors and may stop working completely. It’s important to understand the difference so you know how to act based on the situation at hand.

Database engine considerations

While resource utilization/saturation are very common bottlenecks for database performance, there are limitations within the engine itself. Row-locking contention is a good example, and you can keep an eye on row-lock wait time metrics in tools like PMM. But, much like any other software that allows for concurrent session usage, there are mutexes/semaphores in the code that are used to limit the number of sessions that can access shared resources. Information about this can be found in the semaphores section in the output of the “SHOW ENGINE INNODB STATUS” command.

Unfortunately, this is the single hardest bottleneck to predict and is based solely on the use case. I’ve seen systems running 25,000+ queries per second with no issue, and I’ve also seen systems running ~5,000 queries per second that ran into issues with mutex contention.

Keeping an eye on metrics for OS context switching will help with this a little bit, but unfortunately this is a situation where you normally don’t know where the wall is until you run right into it. Adjusting variables like innodb_thread_concurrency can help with this in a pinch, but when you get to this point, you really need to look at query efficiency and horizontal scaling strategies.

Another thing to consider is configurable hard limits like max_connections, where you can limit the upper bound of the number of connections that can connect to MySQL at any given time. Keep in mind that increasing this value can impact memory consumption as more connections will use more memory, so use caution when adjusting upward.

Conclusion

Capacity planning is not something you do once a year or more as part of a general exercise. It’s not something you do when management calls you to let you know a big sale is coming up that will increase the load on the hosts. It’s part of a regular day-to-day activity for anyone that’s operating in a database administrator role.

Proactive monitoring plays a big part in capacity planning. I’m not talking about alert-based monitoring that hits your pager when it’s already too late, but evaluating metrics usage on a regular basis to see what the data platform is doing, how it’s handling its current traffic, etc. In most cases, you don’t see massive increases in traffic all at once; typically, it’s gradual enough that you can monitor as it increases and adjust your system or processes to avoid saturation.

Tools like PMM and the Percona Toolkit play a big role in proactive monitoring and are open source for free usage. So if you don’t have tools like this in place, this comes in at a price point that makes tool integration easier for your consideration.

Also, if you still feel concerned about your current capacity planning, you can always reach out to Percona Managed Services for a performance review or query review that will give you a detailed analysis of the current state of your database along with recommendations to keep it as performant as possible.

Percona Monitoring and Management is a best-of-breed open source database monitoring solution. It helps you reduce complexity, optimize performance, and improve the security of your business-critical database environments, no matter where they are located or deployed.

 

Download Percona Monitoring and Management Today

Percona Database Performance Blog

TailwindCraft: Free and Open-Source Prebuilt UI Components

https://tailwindcraft.com/storage/photos/1/cover.pngMeticulously designed open-source UI components powered by Tailwind CSS. Streamlines web development with versatile prebuilt elements, seamless Tailwind CSS integration, and a vibrant open-source community.Laravel News Links

A Dive Into toRawSql()


reading up on laravel tricks

Fly.io can build and run your Laravel apps globally, including your scheduled tasks. Deploy your Laravel application on Fly.io, you’ll be up and running in minutes!

In the recent past, we were able to dump out the SQL our query builder was generating like so:

$filter = 'wew, dogs';

// Using the `toSql()` helper
DB::table('foo')
  ->select(['id', 'col1', 'col2'])
  ->join('bar', 'foo.bar_id', 'bar.id')
  ->where('foo.some_colum', $filter)
  ->toSql();

// SELECT id, col1, cole2
//     FROM foo
//     INNER JOIN nar on foo.bar_id = bar.id
//     WHERE foo.some_column = ?

This was useful for debugging complicated queries, but note how we didn’t get the value in our WHERE statement!
All we got was a pesky ? – a placeholder for whatever value we’re passing into the query.
The actual value is hidden from us.

“That’s not what I need”, you may have asked yourself. Assuming we aren’t debugging SQL syntax, our query bindings are what we likely care about the most.

Getting the Full Query

New to Laravel 10.15 is the ability to get the full sql query! That’s much more useful for debugging.

$filter = 'wew, dogs';

// Using the `toRawSql()` helper
DB::table('foo')
  ->select(['id', 'col1', 'col2'])
  ->join('bar', 'foo.bar_id', 'bar.id')
  ->where('foo.some_colum', $filter)
  ->toRawSql();

// SELECT "id", "col1", "col2"
//     FROM "foo" 
//     INNER JOIN "bar" ON "foo"."bar_id" = "bar"."id"
//     WHERE "foo"."some_colum" = 'wew, dogs'"

Much better!

The trick to this is that PDO (the core library used to connect to databases) doesn’t just give this to us – we can only get the SQL with the binding placeholders (hence the old toSql() limitation).

So, we need to build the query with our values within the query ourselves! How’s that done?

How It’s Done

This is tricky business, as we’re dealing with user input – any crazy thing a developer (or their users)
might throw into a sql query needs to be properly escaped.

The new toRawSql() helper stuffs the important logic into a method named substituteBindingsIntoRawSql(). Here’s the PR, for reference.

If we dig into that method code a bit, we’ll
see what’s going on!

The first thing the function does is escape all of the values. This lets Laravel print out the query as a string without worrying about mis-aligned quotes or similar issues.

$bindings = array_map(fn ($value) => $this->escape($value), $bindings);

The call to $this->escape() goes down to a database connection object, and deeper into the underlying PDO object. PDO does the work of
actually escaping the query values in a safe way.

You can get a “Connection Refused” error using the toRawSql() method if your database connection isn’t configured or isn’t working.
That’s because the underlying code uses the “connection” object (PDO under the hood) to escape characters within the output.

Following the escaping-of-values, the method goes through the query character by character!

for ($i = 0; $i < strlen($sql); $i++) {
    $char = $sql[$i];
    $nextChar = $sql[$i + 1] ?? null;

    // and so on
}

The major supported databases use different conventions for escape characters. The code here attempts to find escaped characters and ignore them, lest it tries to
substitute something that looks like a query binding character but isn’t. This is the most fraught bit of code in this new feature.

$query = '';


for ($i = 0; $i < strlen($sql); $i++) {
    $char = $sql[$i];
    $nextChar = $sql[$i + 1] ?? null;

    // Single quotes can be escaped as '' according to the SQL standard while
    // MySQL uses \'. Postgres has operators like ?| that must get encoded
    // in PHP like ??|. We should skip over the escaped characters here.
    if (in_array($char.$nextChar, ["\'", "''", '??'])) {
        // We are building the query string back up - We ignore escaped characters
        // and append them to our rebuilt query string. Since we append
        // two characters, we `$i += 1` so the loop skips $nextChar in our `for` loop
        $query .= $char.$nextChar;
        $i += 1;
    } ...
}

The for loop is rebuilding the query string, but with values substituted in for their ? placeholders. The first check here
is looking for certain escape characters. It needs to know the current character AND the next one to know if it’s an escaped character
and therefore should not do any substitutions.

The next part of our conditional is this:

} elseif ($char === "'") { // Starting / leaving string literal...
    $query .= $char;
    $isStringLiteral = ! $isStringLiteral;
}

If we’re opening an unescaped quote, it means we’re at the start (or end) of a string literal. We set a flag for this case, which
is important in our next check.

elseif ($char === '?' && ! $isStringLiteral) { // Substitutable binding...
    $query .= array_shift($bindings) ?? '?';
}

Here’s the magic. If we’re NOT inside of a string literal, AND we’re not finding an escaped character, AND we find a ? character,
then we can assume it’s a query binding to be substituted with the actual value. We take our array of values and shift it – we remove the
first item in that array and append its value to our query string. (The array of values are in the same order of query binding characters – ? – in the query).

Finally, if we just have a regular character that doesn’t have special meaning, we just append it to our query string:

else { // Normal character...
    $query .= $char;
}

Fly.io ❤️ Laravel

Fly your servers close to your users—and marvel at the speed of close proximity. Deploy globally on Fly in minutes!


Deploy your Laravel app!  

Here’s the whole method, as it stands as I write this:

public function substituteBindingsIntoRawSql($sql, $bindings)
{
    $bindings = array_map(fn ($value) => $this->escape($value), $bindings);

    $query = '';

    $isStringLiteral = false;

    for ($i = 0; $i < strlen($sql); $i++) {
        $char = $sql[$i];
        $nextChar = $sql[$i + 1] ?? null;

        // Single quotes can be escaped as '' according to the SQL standard while
        // MySQL uses \'. Postgres has operators like ?| that must get encoded
        // in PHP like ??|. We should skip over the escaped characters here.
        if (in_array($char.$nextChar, ["\'", "''", '??'])) {
            $query .= $char.$nextChar;
            $i += 1;
        } elseif ($char === "'") { // Starting / leaving string literal...
            $query .= $char;
            $isStringLiteral = ! $isStringLiteral;
        } elseif ($char === '?' && ! $isStringLiteral) { // Substitutable binding...
            $query .= array_shift($bindings) ?? '?';
        } else { // Normal character...
            $query .= $char;
        }
    }

    return $query;
}

That’s basically all there is to the story. We want the entire query to be available with our values!
Here are some (light) caveats we have to get this feature:

  1. We need to make a database connection (thanks to using the PDO library for safe escaping)
  2. The above code MAY contain the occasional bug depending on what values are used
  3. Very long (e.g. binary) values in a query would likely return a complete mess of a query string

Those trade-offs seem fine to me!

Laravel News Links

5 Ways to Retrieve the Last Inserted ID in Laravel

https://laracoding.com/wp-content/uploads/2023/07/5-ways-to-retrieve-the-last-inserted-id-in-laravel_1093.png

In Laravel, after inserting data into a database table, you might need to retrieve the last inserted ID after creating the record. This ID is essential for various tasks, like redirecting users to the newly created resource or performing further operations.

This blog post will guide you through several methods to get the last inserted ID in Laravel by using Eloquent models, the DB facade, or direct access to the PDO instance. Let’s explore the various approaches

Method 1: Retrieving Insert ID Using Model::create

One common way to insert data into the database and get the last inserted ID is by using the create method on an Eloquent model. This method not only inserts the data but also automatically fetches the last inserted ID. You can easily access the ID as a property as: $user->id. Consider the following example:

$user = User::create([
    'name' => 'Steve Rogers',
    'email' => 'captain.america@marvel.com',
    'password' => bcrypt('password123'),
]);

$lastInsertedId = $user->id;

Note that when passing an array of properties to the create function, make sure they are defined as fillable, otherwise the values won’t be written into the database. In our example this means we need to ensure the class User properly defines: protected $fillable = ['name', 'email', 'password'];

Method 2: Retrieving Insert ID Using new Model() and save()

Another approach to insert data and retrieve the last inserted ID is by creating a new instance of the model, setting the attributes, and then calling the save method. You can easily access the ID as a property with $user->id. Let’s consider the following example:

$user = new User();
$user->name = 'Natasha Romanoff';
$user->email = 'black.widow@marvel.com';
$user->password = bcrypt('password123');
$user->save();

$lastInsertedId = $user->id;

Method 3: Retrieving Insert ID Using DB Facade insertGetId()

When you need to insert data without using Eloquent models, you can utilize the insertGetId method provided by the DB facade.

$lastInsertedId = DB::table('users')->insertGetId([
    'name' => 'Bruce Banner',
    'email' => 'hulk@marvel.com',
    'password' => bcrypt('password123'),
    'created_at' =>  \Carbon\Carbon::now(),
    'updated_at' => \Carbon\Carbon::now(),
]);

Note that inserting records using this method will not fill your timestamp values automatically, even if they are defined in your Migration. For this reason we’ve included code to generate them manually using Carbon in the code above.

Method 4: Retrieving Insert ID Using Model::insertGetId

If you are inserting data directly through a model and want to retrieve the last inserted ID, you can use the insertGetId method on the model itself.

$lastInsertedId = User::insertGetId([
    'name' => 'Clint Barton',
    'email' => 'hawkeye@marvel.com',
    'password' => 'password123',
    'created_at' =>  \Carbon\Carbon::now(),
    'updated_at' => \Carbon\Carbon::now(),
]);

Note that inserting records using this method will not fill your timestamp values automatically, even if they are defined in your Migration. For this reason we’ve included code to generate them manually using Carbon in the code above.

Method 5: Direct Access to PDO lastInsertId

You can also directly access the PDO instance to retrieve the last inserted ID.

DB::table('users')->insert([
    'name' => 'Peter Parker',
    'email' => 'spiderman@marvel.com',
    'password' => bcrypt('password123'),
    'created_at' =>  \Carbon\Carbon::now(),
    'updated_at' => \Carbon\Carbon::now(),
]);

$lastInsertedId = DB::getPdo()->lastInsertId();

Note that inserting records using this method will not fill your timestamp values automatically, even if they are defined in your Migration. For this reason, we’ve included code to generate them manually using Carbon in the code above.

Conclusion

By following the methods outlined in this blog post, you can easily obtain the last inserted ID using Eloquent models, the DB facade, or direct access to the PDO instance. Choose the method that suits your needs. However, it’s worth noting that method 1 and method 2 are the most commonly used in Laravel applications. Happy coding!

References

Laravel News Links

Flamethrower Tuba

https://theawesomer.com/photos/2023/08/flaming_tuba_t.jpg

Flamethrower Tuba

Link

There’s nothing inherently dangerous about playing a tuba. Sure, you might run out of breath, but that’s about it. YouTuber and maker MasterMilo has created the most dangerous brass instrument we’ve ever seen. His flamethrower tuba is powered by a chainsaw engine and spews a stream of flaming propane out of its bell.

The Awesomer

PATH settings for Laravel

https://laravelnews.s3.amazonaws.com/images/path-featured.jpg

For Laravel development, we often find ourselves typing commands like ./vendor/bin/pest to run project-specific commands.

We don’t need to!

To help here, we can update our Mac (or Linux) $PATH variable.

What’s $PATH?

The $PATH variable sets the directories your system looks for when finding commands to run.

For example, we can type which <cmd> to find the path to any given command:

$ which git

/usr/local/bin/git

My system knew to find git in /usr/local/bin because /usr/local/bin is one directory set in my $PATH!

You can echo out your path right now:

# Output the whole path

echo $PATH

 

# For human-readability, split out each

# directory into a new line:

echo "$PATH" | tr ':' '\n'

Relative Directories in PATH

We can edit our $PATH variable to add in whatever directories we want!

One extremely handy trick is to set relative directories in your $PATH variable.

Two examples are adding ./vendor/bin and ./node_modules/.bin:

# In your ~/.zshrc, ~/.bashrc or, ~/.bash_profile or similar

# Each directory is separated by a colon

PATH=./vendor/bin:./node_modules/.bin:$PATH

Here we prepended our two new paths to the existing $PATH variable. Now, no matter what Laravel application we’re cded into, we can run pest and know we’re running ./vendor/bin/pest, phpunit to run ./vendor/bin/phpunit (and the same for any given Node command in ./node_modules/.bin).

We can also set the current directory . in our $PATH (if it’s not already set – it may be):

# In your ~/.zshrc, ~/.bashrc or, ~/.bash_profile or similar

# Each directory is separated by a colon

# Here we also set the current directory in our PATH

PATH=.:./vendor/bin:./node_modules/.bin:$PATH

This way we can type artisan instead of ./artisan or php artisan.

These are the settings I have in place in Chipper CI so users can run pest or phpunit without having to worry about where the command exists in their CI environments.

Notes

Order also matters in $PATH. When a command is being searched for, the earlier directories are searched first. The system will use the first command found – this means you can over-ride a system command by placing it in a directory earlier in $PATH. That’s why we prepend ./vendor/bin and ./node_modules/.bin into $PATH instead of append it.

You can find all locations of a command like this:

$ which -a git

 

git is /usr/local/bin/git

git is /usr/bin/git

git is /usr/local/bin/git

git is /usr/bin/git

Lastly, in all cases here, the commands should have executable permissions to work like this. This is something to keep in mind when creating your own commands, such as a custom bash script.

Laravel News