CRUD Operations in PHP and MySQLi Against SQL Injection.

Posted in August 7, 2018 by

Categories: Programming Web Development

Tags: , , , ,

Reading Time: 5 minutes

In this post, I write little about basic operations in PHP on a database, in this case MySQL, which are called CRUD. I am not an expert in this field but I have a little knowledge that I want to share with you. Later I write about prepare statements and little about SQL Injection. The post is not long so you can check it out :-).

My Beginnings with CRUD

My journey with PHP begun about 7 years ago when I bought my first book about PHP and MySQL πŸ™‚ . Then I started to learn from it and started to discover this wonderful language.

I learn mainly functional programming but there were also some object-oriented paradigms. Unfortunately, I don’t remember the title but at the end of this post, I will share links to PHP & MySQL courses I think are good that you can learn from.

What was difficult for me, was to understand the normalization of the database. Its steps and many to many relations in the database 🀨 . Today I know books aren’t such a great medium to learn that stuff for me. A much better is a video course, for example on the Udemy e-learning platform.

I learn what is CRUD and how to apply it in web development. The main task of this book was to create a discussion group framework where CRUD are standard operations πŸ‘ .

About CRUD

CRUD is an abbreviation of create, read, update and delete. Book I was reading was about MySQL also. I mean you cannot have CRUD operations without some database entity in my scenario that was MySQL database.

In this type of operation, you perform tasks on databases like for example when you update your password in some service or delete your account on social media or simply paste a post there. All that kind of stuff is CRUD operations πŸ˜‰ .

CRUD Examples

So how CRUD operations look like with MySQL?

It is very simple and on the other hand, it is not. It depends on how complex and secure your CRUD should be.

In the discussion group, the framework exercise from my book CRUD operations was simplest as they could be. For example:

This could be read instruction, fetching from the database all posts of a user named Bob. In SQL we have some mathematical operators like “=” operator.

So simply we fetch all post data for user Bob here.

SELECT post_title, post_content, post_date FROM posts WHERE username = "Bob";

This could be creating instruction, creating a post of a certain user. Usually, in PHP code we don’t use fixed values like in this example ‘Lorem Ipsum Dolor Sit Amet’.

INSERT INTO posts( post_title, post_content, post_date, post_author ) VALUES ( 'Post 1', 'Lorem Ipsum Dolor Sit Amet', '07-07-2018', 'Bob' );

We use variables instead so proper version of this CRUD read operation would be:

INSERT INTO posts( post_title, post_content, post_date, post_author ) VALUES ( $title', $content, $date, $username );

And this could be update instruction updating your password at some social media site. In this example very important is word WHERE and LIMIT.

It updates password only this user who is logged in and whose session variables are set correctly. 

UPDATE users SET password = "$password" WHERE username_id = $_SESSION['user_id'] LIMIT 1;

You should always put a LIMIT word at the end of an UPDATE and DELETE SQL statement because it prevents updating or deleting all records at once ☝️ πŸ€“ .

Last of the CRUD instruction is delete. It could be use for deleting user account when we click delete button. And again here also it is better to use LIMIT 1 word just because we want to delete only 1 record in most cases.

DELETE FROM users WHERE user_id = $id LIMIT 1;

Why Do We Need MySQLi Prepare Statements?

This is a very useful extension of normal MySQL and provides interfaces for more secure CRUD operations.

You can always use standard MySQL interfaces but MySQLi is more secure and with prepare statements, it is preventing SQL Injection attacks.

Prepare statements are SQL instructions which prevent this kind of attacks by binding passed to instruction values with SQL parameters we prepare earlier.

Let’s assume we have logging in functionality on page with 2 input fields. One for email and one for a password.

Logging in Functionality Image

If we would use simple CRUD operation for logging user attacker could use a very simple SQL Injection attack to log in on our account ☝️ .

For example, if we use this SQL statement to fetch login user from database:

SELECT username, password FROM usersΒ  WHERE username = '$username' AND password = '$password';

An attacker could amend this statement writing in password field “OR 1=1” and in result, we would have:

SELECT username, password FROM usersΒ  WHERE username = '$username' AND password = '' OR 1=1';

Now database will return also 1 row and attacker will be logged in 😒 .

Simple MySQLi Prepare Statements

To have more secure CRUD we use prepare statements. Its construction is simple. We declare SQL statement without any variables but where we would put our data we just put a question mark and then bind with this our data.

$stmt = $conn->prepare("INSERT INTO users(username, password) VALUES (?, ?)"); 
$stmt->bind_param("ss", $username, $password); 
$stmt->execute();

In prepare stage SQL statement is sent to database server and its syntax is checked. Then in execute stage, bound data is sent to a database server where SQL statement is executed. So we have two stages.

In a database server, SQL template is created only once so every time a query is sent by a user a statement is not parsed again.

Conclusions

Since I discover MySQLi and prepare statements I try to use this standard on a daily basis. It is more secure and more reliable. Sometimes it is more difficult to understand a query for the first time and it takes more time to read source code but advantages exceed flaws I think.

If you find this post useful and informative you can leave a comment, like it, or share it on social media 😊 . If you will find any mistake don’t hesitate to note it in a comment or send me a direct message through social media or contact form πŸ‘ .

Goof Bye Image for Post
Image 1. Good Bye Image.

Have a great day πŸ‘‹ .

Udemy Courses I recommend:


Leave a Reply

Your email address will not be published. Required fields are marked *

five + eight =