Show
SQL Injection is a common problem that arises due to loopholes in the backend programming. There are many methods that can be used to avoid PHP SQL Injection attacks in a website. Web developers use different tactics and logic to find out vulnerabilities and their possible solutions. Nowadays you might have heard the term TDD and BDD. These both are development processes used in programming to assure the maximum security with testing while developing in different languages. Read More About: Ultimate Guide to PHP Security Best Practices In this article, we will be discussing a few scenarios of SQL Injection and how to prevent them in web apps deployed on different PHP web hosting servers.
But first, it is important to know what actually SQL Injection is and what harm it causes. But before let’s understand a few concepts.
Keep Your Apps Secure on CloudCloudways offers 2FA, free SSL, and more advanced security features on managed servers that keep your application safe. What is TDD?Test Driven Development is a practice in which you write a test first and code later to pass the test. This approach makes sure that the number of bugs must be solved while development. The developer tightly writes the test cases first and then the code, as this practice enables him to quickly find out the potential bugs. The process is as follows:
Read More About: PHP Unit Testing With PHPUnit You can clearly see in the above process that we are writing the tests first and then passing the code from it. If the test passes then the cycle will go in a loop. See the visual representation below: Read More About: Test and Deploy With TravisCI What Is Exactly SQL Injection?SQL Injection is a technique used by the hackers to change SQL statements running at the backend from forged executed SQL commands. Such kind of injections is usually done through input fields of the form causing a bad effect on database. This results in loss of sensitive information from the database. Through such tactics, attackers input vulnerable data to SQL interpreter that executes unintended commands. By such PHP MySQL injections, attackers may insert, update or even delete data from database. Improve Your PHP App Speed by 300%Cloudways offers you dedicated servers with SSD storage, custom performance, an optimized stack, and more for 300% faster load times. Cause Of SQL InjectionWhile coding we should follow best practices to avoid SQL injection in PHP. Some of the causes which can affect these attacks are:
So these are some causes, you might watch out while coding to avoid SQL injections. Let’s See The ExamplesThe following PHP SQL injection example will help you better understand the concept of SQL injections: Example # 1Suppose we have a form containing 2 text fields’ username and password, along with a login button. The backend PHP code will be as follows: <?php $userName=$_POST['userName']; $password=$_POST['password']; $sqlQuery="SELECT * FROM users WHERE user_name='".$username."' AND user_password='".$password"';"; ?> The above code contains a loophole, if a user enters ‘ or ‘a’=’a ‘or’ then the variable $password will have the value ‘ or ‘a’=’a ‘or’ In this way, the above query will be updated as: <?php $sqlQuery="SELECT * FROM users WHERE user_name='".$username."' AND user_password='' or 'a'='a';"; ?> In the above example, the statement a=a is always true. So the statement is executed without the matching of the actual password. Example # 2The SQL query is a legitimate program. And we are creating such a program dynamically, by adding some data on the fly. This data can interfere within the program code and can even alter it, as every SQL injection example shows it (all examples in PHP/Mysql): $expected_data = 1; $query = "SELECT * FROM users where id=$expected_data"; will produce a regular query SELECT * FROM users where id=1 while this code can surprise you. $spoiled_data = "1; DROP TABLE users;" $query = "SELECT * FROM users where id=$spoiled_data"; will produce a malicious sequence SELECT * FROM users where id=1; DROP TABLE users; It works because we are adding the data directly to the program body and it becomes a part of the program, so the data may alter the program and depend on the data passed, we will have either a regular output or a table user deleted. Solutions to SQL injection vulnerabilitiesThe SQL injection protection in PHP is quite a complexed topic. In this post, I am demonstrating two methods through which you can solve this issue. Method 1Now you need to make a few changes in the previous code. Make a function like: <?php function BlockSQLInjection($str) { return str_replace(array("'",""","'",'"'),array("'","""'",""",$str)); } ?> Through the above statement, str_replace() function will replace all characters in the string. Now you will use the function as follows: <?php $userName=BlockSQLInjection($_POST['userName']); $password=BlockSQLInjection($_POST['password']); ?> These functions will help you avoid SQL injection vulnerabilities. Method 2Another approach for avoiding SQL injections is using PHP Prepared Statements. A prepared statement is a feature in PHP which enables users to execute similar SQL queries efficiently and repeatedly. Read More About: Learn How to Use Prepared Statements Through prepared statements, SQL query is sent to the database with a few unspecified values called parameters denoted by ‘?’. The database then compiles it and stores the result without executing. Afterward, the application binds values to the parameters before finally executing the statement. This enables execution of the statement repeatedly with a different set of values. Advantages of Executing Prepare Statements
ExampleConsider the following example: <?php $stmt=$conn->prepare(INSERT INTO MyGuests(firstname,lastname,email)VALUES(?,?,?)"); $stmt->bind_param("sss",$firstname,$lastname,$email); //set paramters and execute $firstname="John"; $lastname="Doe"; $email="[email protected]"; $stmt->execute(); $firstname="Mary"; $lastname="Moe"; $email="[email protected]"; $stmt->execute(); ?> In the above example, you can see in line 14 that the insert statement contains values (?,?,?). It indicates that we can substitute integer, double, string or blob value. Now consider line 15 containing bind_param. This function basically binds different parameters to the query and conveys parameters to the database. ‘sss’ is an argument which basically lists the type of data. Argument may be integer(i), double(d), string(s), BLOB(b). By telling the database what type of data to expect, we basically minimize risk of SQL injection. Tips For Avoiding PHP SQL Injection VulnerabilitiesPrevention is better than cure. You must take the following precautions as these are the best ways to prevent SQL injection in php:
Share your opinion in the comment section. COMMENT NOW Share This ArticleCustomer Review at “Cloudways hosting has one of the best customer service and hosting speed”Sanjit C [Website Developer] Ahmed KhanAhmed was a PHP community expert at Cloudways - A Managed PHP Hosting Cloud Platform. He is a software engineer with extensive knowledge in PHP and SEO. He loves watching Game of Thrones is his free time. Follow Ahmed on Twitter to stay updated with his works. You can email him at [email protected] × Get Our Newsletter Be the first to get the latest updates and tutorials.Thankyou for Subscribing Us!How safe PHP files prevent the SQL injection attacks?PHP has a specially-made function to prevent these attacks. All you need to do is use the mouthful of a function, mysql_real_escape_string . mysql_real_escape_string takes a string that is going to be used in a MySQL query and return the same string with all SQL injection attempts safely escaped.
What is the preferred method for preventing SQL injection PHP?The only sure way to prevent SQL Injection attacks is input validation and parametrized queries including prepared statements. The application code should never use the input directly. The developer must sanitize all input, not only web form inputs such as login forms.
What is PHP injection attack?PHP Object Injection is an application level vulnerability that could allow an attacker to perform different kinds of malicious attacks, such as Code Injection, SQL Injection, Path Traversal and Application Denial of Service, depending on the context.
What is SQL injection explain methods to prevent it with example in PHP?SQL injection is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution (e.g. to dump the database contents to the attacker).
|