CODEWITHSUNDEEP
phpmysqlmysqlipdo
Ahmad Sqalli
Ahmad Sqalli

Reputation: 69

Changing from MySQL to MySQLi or PDO

I have been working with PHP and MySQL for almost two years and I find that mysql is very easy to use, but it has security issues, and that's why I'm going to change from mysql to MySQLi, and I was wondering: is MySQLi more secure than the mysql?

And about MySQLi query: Should I just add an ' i ' to the old mysql like the following (just an example)?

mysql

if (mysql_query('insert into users (username, password, regdate)
                 values ("'.$username.'", "'.$password.'", "'.time().'")')) {
    echo 'Inserted!';
}
else
{
    echo "something is wrong";
}

to MySQLi:

if (mysqli_query('insert into users (username, password, regdate)
                  values ("'.$username.'", "'.$password.'", "'.time().'")')) {
    echo 'Inserted!';
}
else {
    echo "something is wrong";
}

I've also heard about PDO and again: is PDO more secure than MySQLi?

Upvotes: 0

Views: 246

Answers (2)

CaringDev
CaringDev

Reputation: 8551

If you are going to concatenate your queries, the security of the technology used does not matter much... That said, there is Choosing an API.

Upvotes: 2

ArtisticPhoenix
ArtisticPhoenix

Reputation: 21661

The security issues come from what is known as SQL injection. In order to understand how to make it more secure you have to first understand how that works. Using a basic example of a really bad query:

   mysql_query('SELECT FROM user WHERE username="'.$_POST['username'].'" AND password="'.$_POST['username'].'"');

There are two main things wrong with this query. The first and most obvious is that values are added with no sanitation done on them. This is a classic example of SQL injection. In this example a user could submit a username, like this..

 'admin" --';

Now what is so bad with that? Because we are not filtering this (mainly for the quote) it makes our query look like this:

 SELECT * FROM user WHERE username="admin" --" AND password="'.$_POST['username'].'"

To fully understand this you have to know that the -- is the start of an inline comment ( much like // in PHP), and nothing after it is ran by the database. Given that, this is what we are left with.

 SELECT * FROM user WHERE username="admin"

Now if we were doing that for password validation by seeing if it returned a row, and had an account named admin which isn't unreasonable, we would have someone logged in without ever using a password.

The second issue that is less obvious is sending the password and using the query to validate. If we had structured it this way (psudo code):

   SELECT password FROM user WHERE username="admin"
   if( returned password == submitted password )

They would still need a password, because we are doing the evaluation in our code (application layer) and not in the database.

mysql, mysqli or PDO will not protect you if you inject variables. PDO and MySQLi allow you to use prepared statements that take care of that issue. PDO is the better of the two if you ask me for the following reasons.

  • Not dependent on the MySQL database (database agnostic)
  • Allows named place holders, such as :name instead or just ? indexed placeholders
  • Better OOP support
  • Generally supports more features

But neither one can keep you safe if you don't understand the threat.

Upvotes: 1

Related Questions