Changing from MySQL to MySQLi or PDO

Question

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?

best to read the manual before attempting to switch to mysqli http://php.net/manual/en/mysqli.query.php — Funk Forty Niner, Aug 05 '15 at 22:30
mysqli you provided is as unsecure as your mysql. You need to use prepared statements to protect you from SQL injection" http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — user4035, Aug 05 '15 at 22:40
possible duplicate of [mysqli or PDO - what are the pros and cons?](http://stackoverflow.com/questions/13569/mysqli-or-pdo-what-are-the-pros-and-cons) — nomistic, Aug 05 '15 at 23:26
You can't just add an `i` to your `mysql` functions. Take a look at [this answer](http://stackoverflow.com/a/26476208/3899908) for a few examples of what you need to change to. — worldofjr, Aug 06 '15 at 01:38

score 2 · Answer 1 · edited Feb 13 '16 at 11:40

2

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.

edited Feb 13 '16 at 11:40

Peter Mortensen

30,738
21
105
131

answered Aug 05 '15 at 22:36

CaringDev

8,391
1
24
43

score 1 · Accepted Answer · edited Feb 13 '16 at 11:39

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.

Sure thing. Sometimes it just takes a simple explanation, and then it all clicks. Glad I could help point you down the road, keep trucking. — ArtisticPhoenix, Aug 06 '15 at 01:32

Changing from MySQL to MySQLi or PDO

2 Answers2