Friday 5 October 2018

Why shouldn't I use mysql_* functions in PHP?

What are the technical reasons for why one shouldn't use mysql_* functions? (e.g. mysql_query(), mysql_connect() or mysql_real_escape_string())?
Why should I use something else even if they work on my site?
If they don't work on my site, why do I get errors like
Warning: mysql_connect(): No such file or directory
The MySQL extension:
  • Is not under active development
  • Is officially deprecated as of PHP 5.5 (released June 2013).
  • Has been removed entirely as of PHP 7.0 (released December 2015)
    • This means that as of 31 Dec 2018 it will not exist in any supported version of PHP. Currently, it only gets security updates.
  • Lacks an OO interface
  • Doesn't support:
    • Non-blocking, asynchronous queries
    • Prepared statements or parameterized queries
    • Stored procedures
    • Multiple Statements
    • Transactions
    • The "new" password authentication method (on by default in MySQL 5.6; required in 5.7)
    • All of the functionality in MySQL 5.1
Since it is deprecated, using it makes your code less future proof.
Lack of support for prepared statements is particularly important as they provide a clearer, less error-prone method of escaping and quoting external data than manually escaping it with a separate function call.

The mysql_ functions:
  1. are out of date - they're not maintained any more
  2. don't allow you to move easily to another database backend
  3. don't support prepared statements, hence
  4. encourage programmers to use concatenation to build queries, leading to SQL injection vulnerabilities


There are many reasons, but perhaps the most important one is that those functions encourage insecure programming practices because they do not support prepared statements. Prepared statements help prevent SQL injection attacks.
When using mysql_* functions, you have to remember to run user-supplied parameters through mysql_real_escape_string(). If you forget in just one place or if you happen to escape only part of the input, your database may be subject to attack.
Using prepared statements in PDO or mysqli will make it so that these sorts of programming errors are more difficult to make.


This answer is written to show just how trivial it is to bypass poorly written PHP user-validation code, how (and using what) these attacks work and how to replace the old MySQL functions with a secure prepared statement - and basically, why users (probably with a lot of rep) are barking at new users asking questions to improve their code.
First off, please feel free to create this test mysql database (I have called mine prep):
mysql> create table users(
    -> id int(2) primary key auto_increment,
    -> userid tinytext,
    -> pass tinytext);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into users values(null, 'Fluffeh', 'mypass');
Query OK, 1 row affected (0.04 sec)

mysql> create user 'prepared'@'localhost' identified by 'example';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on prep.* to 'prepared'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)
With that done, we can move to our PHP code.
Lets assume the following script is the verification process for an admin on a website (simplified but working if you copy and use it for testing):
<?php 

    if(!empty($_POST['user']))
    {
        $user=$_POST['user'];
    }   
    else
    {
        $user='bob';
    }
    if(!empty($_POST['pass']))
    {
        $pass=$_POST['pass'];
    }
    else
    {
        $pass='bob';
    }

    $database='prep';
    $link=mysql_connect('localhost', 'prepared', 'example');
    mysql_select_db($database) or die( "Unable to select database");

    $sql="select id, userid, pass from users where userid='$user' and pass='$pass'";
    //echo $sql."<br><br>";
    $result=mysql_query($sql);
    $isAdmin=false;
    while ($row = mysql_fetch_assoc($result)) {
        echo "My id is ".$row['id']." and my username is ".$row['userid']." and lastly, my password is ".$row['pass']."<br>";
        $isAdmin=true;
        // We have correctly matched the Username and Password
        // Lets give this person full access
    }
    if($isAdmin)
    {
        echo "The check passed. We have a verified admin!<br>";
    }
    else
    {
        echo "You could not be verified. Please try again...<br>";
    }
    mysql_close($link);

?>

<form name="exploited" method='post'>
    User: <input type='text' name='user'><br>
    Pass: <input type='text' name='pass'><br>
    <input type='submit'>
</form>
Seems legit enough at first glance.
The user has to enter a login and password, right?
Brilliant, not enter in the following:
user: bob
pass: somePass
and submit it.
The output is as follows:
You could not be verified. Please try again...
Super! Working as expected, now lets try the actual username and password:
user: Fluffeh
pass: mypass
Amazing! Hi-fives all round, the code correctly verified an admin. It's perfect!
Well, not really. Lets say the user is a clever little person. Lets say the person is me.
Enter in the following:
user: bob
pass: n' or 1=1 or 'm=m
And the output is:
The check passed. We have a verified admin!
Congrats, you just allowed me to enter your super-protected admins only section with me entering a false username and a false password. Seriously, if you don't believe me, create the database with the code I provided, and run this PHP code - which at glance REALLY does seem to verify the username and password rather nicely.
So, in answer, THAT IS WHY YOU ARE BEING YELLED AT.
So, lets have a look at what went wrong, and why I just got into your super-admin-only-bat-cave. I took a guess and assumed that you weren't being careful with your inputs and simply passed them to the database directly. I constructed the input in a way tht would CHANGE the query that you were actually running. So, what was it supposed to be, and what did it end up being?
select id, userid, pass from users where userid='$user' and pass='$pass'
That's the query, but when we replace the variables with the actual inputs that we used, we get the following:
select id, userid, pass from users where userid='bob' and pass='n' or 1=1 or 'm=m'
See how I constructed my "password" so that it would first close the single quote around the password, then introduce a completely new comparison? Then just for safety, I added another "string" so that the single quote would get closed as expected in the code we originally had.
However, this isn't about folks yelling at you now, this is about showing you how to make your code more secure.
Okay, so what went wrong, and how can we fix it?
This is a classic SQL injection attack. One of the simplest for that matter. On the scale of attack vectors, this is a toddler attacking a tank - and winning.
So, how do we protect your sacred admin section and make it nice and secure? The first thing to do will be to stop using those really old and deprecated mysql_* functions. I know, you followed a tutorial you found online and it works, but it's old, it's outdated and in the space of a few minutes, I have just broken past it without so much as breaking a sweat.
Now, you have the better options of using mysqli_ or PDO. I am personally a big fan of PDO, so I will be using PDO in the rest of this answer. There are pro's and con's, but personally I find that the pro's far outweigh the con's. It's portable across multiple database engines - whether you are using MySQL or Oracle or just about bloody anything - just by changing the connection string, it has all the fancy features we want to use and it is nice and clean. I like clean.
Now, lets have a look at that code again, this time written using a PDO object:
<?php 

    if(!empty($_POST['user']))
    {
        $user=$_POST['user'];
    }   
    else
    {
        $user='bob';
    }
    if(!empty($_POST['pass']))
    {
        $pass=$_POST['pass'];
    }
    else
    {
        $pass='bob';
    }
    $isAdmin=false;

    $database='prep';
    $pdo=new PDO ('mysql:host=localhost;dbname=prep', 'prepared', 'example');
    $sql="select id, userid, pass from users where userid=:user and pass=:password";
    $myPDO = $pdo->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
    if($myPDO->execute(array(':user' => $user, ':password' => $pass)))
    {
        while($row=$myPDO->fetch(PDO::FETCH_ASSOC))
        {
            echo "My id is ".$row['id']." and my username is ".$row['userid']." and lastly, my password is ".$row['pass']."<br>";
            $isAdmin=true;
            // We have correctly matched the Username and Password
            // Lets give this person full access
        }
    }

    if($isAdmin)
    {
        echo "The check passed. We have a verified admin!<br>";
    }
    else
    {
        echo "You could not be verified. Please try again...<br>";
    }

?>

<form name="exploited" method='post'>
    User: <input type='text' name='user'><br>
    Pass: <input type='text' name='pass'><br>
    <input type='submit'>
</form>
The major differences are that there are no more mysql_* functions. It's all done via a PDO object, secondly, it is using a prepared statement. Now, what's a prepred statement you ask? It's a way to tell the database ahead of running a query, what the query is that we are going to run. In this case, we tell the database: "Hi, I am going to run a select statement wanting id, userid and pass from the table users where the userid is a variable and the pass is also a variable.".
Then, in the execute statement, we pass the database an array with all the variables that it now expects.
The results are fantastic. Lets try those username and password combinations from before again:
user: bob
pass: somePass
User wasn't verified. Awesome.
How about:
user: Fluffeh
pass: mypass
Oh, I just got a little excited, it worked: The check passed. We have a verified admin!
Now, lets try the data that a clever chap would enter to try to get past our little verification system:
user: bob
pass: n' or 1=1 or 'm=m
This time, we get the following:
You could not be verified. Please try again...
This is why you are being yelled at when posting questions - it's because people can see that your code can be bypassed wihout even trying. Please, do use this question and answer to improve your code, to make it more secure and to use functions that are current.
Lastly, this isn't to say that this is PERFECT code. There are many more things that you could do to improve it, use hashed passwords for example, ensure that when you store sensetive information in the database, you don't store it in plain text, have multiple levels of verification - but really, if you just change your old injection prone code to this, you will be WELL along the way to writing good code - and the fact that you have gotten this far and are still reading gives me a sense of hope that you will not only implement this type of code when writing your websites and applications, but that you might go out and research those other things I just mentioned - and more. Write the best code you can, not the most basic code that barely functions.


I find the above answers really lengthy, so to summarize:
The mysqli extension has a number of benefits, the key enhancements over the mysql extension being:
  • Object-oriented interface
  • Support for Prepared Statements
  • Support for Multiple Statements
  • Support for Transactions
  • Enhanced debugging capabilities
  • Embedded server support
Source: MySQLi overview
As explained in the above answers, the alternatives to mysql are mysqli and PDO (PHP Data Objects).
  • API supports server-side Prepared Statements: Supported by MYSQLi and PDO
  • API supports client-side Prepared Statements: Supported only by PDO
  • API supports Stored Procedures: Both MySQLi and PDO
  • API supports Multiple Statements and all MySQL 4.1+ functionality - Supported by MySQLi and mostly also by PDO
Both MySQLi and PDO were introduced in PHP 5.0, whereas MySQL was introduced prior to PHP 3.0. A point to note is that MySQL is included in PHP5.x though deprecated in later versions.


The functions which are as similar to this mysql_connect(), mysql_query() type are the previous version PHP i.e(PHP 4) functions and now not in use .
These are replaced by mysqli_connect(), mysqli_query() similarly in the latest PHP5.
This is the reason behind the error. 

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)