Why shouldn t I use mysql functions in PHP

Question

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

User · Answer

Ease of use

The analytic and synthetic reasons were already mentioned. For newcomers there's a more significant incentive to stop using the dated mysql_ functions.

Contemporary database APIs are just easier to use.

It's mostly the bound parameters which can simplify code. And with excellent tutorials (as seen above) the transition to PDO isn't overly arduous.

Rewriting a larger code base at once however takes time. Raison d'être for this intermediate alternative:

Equivalent pdo_* functions in place of mysql_*

Using <pdo_mysql.php> you can switch from the old mysql_ functions with minimal effort. It adds pdo_ function wrappers which replace their mysql_ counterparts.

Simply include_once("pdo_mysql.php"); in each invocation script that has to interact with the database.
Remove the ~~mysql_~~ function prefix everywhere and replace it with pdo_.
- ~~mysql_~~connect() becomes pdo_connect()
- ~~mysql_~~query() becomes pdo_query()
- ~~mysql_~~num_rows() becomes pdo_num_rows()
- ~~mysql_~~insert_id() becomes pdo_insert_id()
- ~~mysql_~~fetch_array() becomes pdo_fetch_array()
- ~~mysql_~~fetch_assoc() becomes pdo_fetch_assoc()
- ~~mysql_~~real_escape_string() becomes pdo_real_escape_string()
- and so on...

Your code will work alike and still mostly look the same:

include_once("pdo_mysql.php"); 

pdo_connect("localhost", "usrABC", "pw1234567");
pdo_select_db("test");

$result = pdo_query("SELECT title, html FROM pages");  

while ($row = pdo_fetch_assoc($result)) {
    print "$row[title] - $row[html]";
}

Et voilà.
Your code is using PDO.
Now it's time to actually utilize it.

Bound parameters can be easy to use

You just need a less unwieldy API.

pdo_query() adds very facile support for bound parameters. Converting old code is straightforward:

Move your variables out of the SQL string.

Add them as comma delimited function parameters to pdo_query().
Place question marks ? as placeholders where the variables were before.
Get rid of ' single quotes that previously enclosed string values/variables.

The advantage becomes more obvious for lengthier code.

Often string variables aren't just interpolated into SQL, but concatenated with escaping calls in between.

pdo_query("SELECT id, links, html, title, user, date FROM articles
   WHERE title='" . pdo_real_escape_string($title) . "' OR id='".
   pdo_real_escape_string($title) . "' AND user <> '" .
   pdo_real_escape_string($root) . "' ORDER BY date")

With ? placeholders applied you don't have to bother with that:

pdo_query("SELECT id, links, html, title, user, date FROM articles
   WHERE title=? OR id=? AND user<>? ORDER BY date", $title, $id, $root)

Remember that pdo_* still allows either or.
Just don't escape a variable and bind it in the same query.

The placeholder feature is provided by the real PDO behind it.
Thus also allowed :named placeholder lists later.

More importantly you can pass $_REQUEST[] variables safely behind any query. When submitted <form> fields match the database structure exactly it's even shorter:

pdo_query("INSERT INTO pages VALUES (?,?,?,?,?)", $_POST);

So much simplicity. But let's get back to some more rewriting advises and technical reasons on why you may want to get rid of ~~mysql_~~ and escaping.

Fix or remove any oldschool `sanitize()` function

Once you have converted all ~~mysql_~~ calls to pdo_query with bound params, remove all redundant pdo_real_escape_string calls.

In particular you should fix any sanitize or clean or filterThis or clean_data functions as advertised by dated tutorials in one form or the other:

function sanitize($str) {
   return trim(strip_tags(htmlentities(pdo_real_escape_string($str))));
}

Most glaring bug here is the lack of documentation. More significantly the order of filtering was in exactly the wrong order.

Correct order would have been: deprecatedly stripslashes as the innermost call, then trim, afterwards strip_tags, htmlentities for output context, and only lastly the _escape_string as its application should directly preceed the SQL intersparsing.
But as first step just get rid of the _real_escape_string call.
You may have to keep the rest of your sanitize() function for now if your database and application flow expect HTML-context-safe strings. Add a comment that it applies only HTML escaping henceforth.
String/value handling is delegated to PDO and its parameterized statements.
If there was any mention of stripslashes() in your sanitize function, it may indicate a higher level oversight.
- That was commonly there to undo damage (double escaping) from the deprecated magic_quotes. Which however is best fixed centrally, not string by string.
- Use one of the userland reversal approaches. Then remove the stripslashes() in the sanitize function.
Historic note on magic_quotes. That feature is rightly deprecated. It's often incorrectly portrayed as failed security feature however. But magic_quotes are as much a failed security feature as tennis balls have failed as nutrition source. That simply wasn't their purpose.

The original implementation in PHP2/FI introduced it explicitly with just "quotes will be automatically escaped making it easier to pass form data directly to msql queries". Notably it was accidentially safe to use with mSQL, as that supported ASCII only.
Then PHP3/Zend reintroduced magic_quotes for MySQL and misdocumented it. But originally it was just a convenience feature, not intend for security.

How prepared statements differ

When you scramble string variables into the SQL queries, it doesn't just get more intricate for you to follow. It's also extraneous effort for MySQL to segregate code and data again.

SQL injections simply are when data bleeds into code context. A database server can't later spot where PHP originally glued variables inbetween query clauses.

With bound parameters you separate SQL code and SQL-context values in your PHP code. But it doesn't get shuffled up again behind the scenes (except with PDO::EMULATE_PREPARES). Your database receives the unvaried SQL commands and 1:1 variable values.

While this answer stresses that you should care about the readability advantages of dropping ~~mysql_~~. There's occasionally also a performance advantage (repeated INSERTs with just differing values) due to this visible and technical data/code separation.

Beware that parameter binding still isn't a magic one-stop solution against all SQL injections. It handles the most common use for data/values. But can't whitelist column name / table identifiers, help with dynamic clause construction, or just plain array value lists.

Hybrid PDO use

These pdo_* wrapper functions make a coding-friendly stop-gap API. (It's pretty much what MYSQLI could have been if it wasn't for the idiosyncratic function signature shift). They also expose the real PDO at most times.
Rewriting doesn't have to stop at using the new pdo_ function names. You could one by one transition each pdo_query() into a plain $pdo->prepare()->execute() call.

It's best to start at simplifying again however. For example the common result fetching:

$result = pdo_query("SELECT * FROM tbl");
while ($row = pdo_fetch_assoc($result)) {

Can be replaced with just an foreach iteration:

foreach ($result as $row) {

Or better yet a direct and complete array retrieval:

$result->fetchAll();

You'll get more helpful warnings in most cases than PDO or mysql_ usually provide after failed queries.

Other options

So this hopefully visualized some practical reasons and a worthwile pathway to drop ~~mysql_~~.

Just switching to pdo doesn't quite cut it. pdo_query() is also just a frontend onto it.

Unless you also introduce parameter binding or can utilize something else from the nicer API, it's a pointless switch. I hope it's portrayed simple enough to not further the discouragement to newcomers. (Education usually works better than prohibition.)

While it qualifies for the simplest-thing-that-could-possibly-work category, it's also still very experimental code. I just wrote it over the weekend. There's a plethora of alternatives however. Just google for PHP database abstraction and browse a little. There always have been and will be lots of excellent libraries for such tasks.

If you want to simplify your database interaction further, mappers like Paris/Idiorm are worth a try. Just like nobody uses the bland DOM in JavaScript anymore, you don't have to babysit a raw database interface nowadays.

User · Answer

The mysql  functions    are out of date - they re not maintained any more don t allow you to move easily to another database backend don t support prepared statements  hence encourage programmers to use concatenation to build queries  leading to SQL injection vulnerabilities

User · Answer

First  let s begin with the standard comment we give everyone       Please  don t use mysql   functions in new code  They are no longer maintained and are officially deprecated  See the red box  Learn about prepared statements instead  and use PDO or MySQLi - this article will help you decide which  If you choose PDO  here is a good tutorial    Let s go through this  sentence by sentence  and explain    They are no longer maintained  and are officially deprecated  This means that the PHP community is gradually dropping support for these very old functions  They are likely to not exist in a future  recent  version of PHP  Continued use of these functions may break your code in the  not so  far future   NEW  - ext mysql is now officially deprecated as of PHP 5 5   Newer  ext mysql has been removed in PHP 7  Instead  you should learn of prepared statements  mysql   extension does not support prepared statements  which is  among other things  a very effective countermeasure against SQL Injection  It fixed a very serious vulnerability in MySQL dependent applications which allows attackers to gain access to your script and perform any possible query on your database   For more information  see How can I prevent SQL injection in PHP  See the Red Box   When you go to any mysql function manual page  you see a red box  explaining it should not be used anymore  Use either PDO or MySQLi  There are better  more robust and well-built alternatives  PDO - PHP Database Object  which offers a complete OOP approach to database interaction  and MySQLi  which is a MySQL specific improvement

User · Answer

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

User · Answer

The MySQL extension is the oldest of the three and was the original way that developers used to communicate with MySQL  This extension is now being deprecated in favor of the other two alternatives because of improvements made in newer releases of both PHP and MySQL    MySQLi is the  improved  extension for working with MySQL databases  It takes advantage of features that are available in newer versions of the MySQL server  exposes both a function-oriented and an object-oriented interface to the developer and a does few other nifty things  PDO offers an API that consolidates most of the functionality that was previously spread across the major database access extensions  i e  MySQL  PostgreSQL  SQLite  MSSQL  etc  The interface exposes high-level objects for the programmer to work with database connections  queries and result sets  and low-level drivers perform communication and resource handling with the database server  A lot of discussion and work is going into PDO and it   s considered the appropriate method of working with databases in modern  professional code

User · Answer

Speaking of technical reasons  there are only a few  extremely specific and rarely used  Most likely you will never ever use them in your life  Maybe I am too ignorant  but I never had an opportunity to use them things like    non-blocking  asynchronous queries stored procedures returning multiple resultsets Encryption  SSL  Compression   If you need them - these are no doubt technical reasons to move away from mysql extension toward something more stylish and modern-looking   Nevertheless  there are also some non-technical issues  which can make your experience a bit harder   further use of these functions with modern PHP versions will raise deprecated-level notices  They simply can be turned off  in a distant future  they can be possibly removed from the default PHP build  Not a big deal too  as mydsql ext will be moved into PECL and every hoster will be happy to compile PHP with it  as they don t want to lose clients whose sites were working for decades     strong resistance from Stackoverflow community   verytime you mention these honest functions  you being told that they are under strict taboo  being an average PHP user  most likely your idea of using these functions is error-prone and wrong  Just because of all these numerous tutorials and manuals which teach you the wrong way  Not the functions themselves - I have to emphasize it - but the way they are used    This latter issue is a problem  But  in my opinion  the proposed solution is no better either  It seems to me too idealistic a dream that all those PHP users will learn how to handle SQL queries properly at once  Most likely they would just change mysql   to mysqli   mechanically  leaving the approach the same  Especially because mysqli makes prepared statements usage incredible painful and troublesome  Not to mention that native prepared statements aren t enough to protect from SQL injections  and neither mysqli nor PDO offers a solution    So  instead of fighting this honest extension  I d prefer to fight wrong practices and educate people in the right ways    Also  there are some false or non-significant reasons  like   Doesn t support Stored Procedures  we were using mysql query  CALL my proc    for ages  Doesn t support Transactions  same as above  Doesn t support Multiple Statements  who need them   Not under active development  so what  does it affect you in any practical way   Lacks an OO interface  to create one is a matter of several hours  Doesn t support Prepared Statements or Parametrized Queries   The last one is an interesting point  Although mysql ext do not support native prepared statements  they aren t required for the safety  We can easily fake prepared statements using manually handled placeholders  just like PDO does    function paraQuery          args    func get args         query   array shift  args        query   str replace   s     s    query         foreach   args as  key   gt   val                 args  key    mysql real escape string  val               query    vsprintf  query   args        result   mysql query  query       if    result                throw new Exception mysql error       query               return  result      query     SELECT   FROM table where a  s AND b LIKE  s LIMIT  d    result   paraQuery  query   a     b     limit     voila  everything is parameterized and safe   But okay  if you don t like the red box in the manual  a problem of choice arises  mysqli or PDO   Well  the answer would be as follows    If you understand the necessity of using a database abstraction layer and looking for an API to create one  mysqli is a very good choice  as it indeed supports many mysql-specific features  If  like vast majority of PHP folks  you are using raw API calls right in the application code  which is essentially wrong practice  - PDO is the only choice  as this extension pretends to be not just API but rather a semi-DAL  still incomplete but offers many important features  with two of them makes PDO critically distinguished from mysqli    unlike mysqli  PDO can bind placeholders by value  which makes dynamically built queries feasible without several screens of quite messy code  unlike mysqli  PDO can always return query result in a simple usual array  while mysqli can do it only on mysqlnd installations     So  if you are an average PHP user and want to save yourself a ton of headaches when using native prepared statements  PDO - again - is the only choice  However  PDO is not a silver bullet too and has its hardships  So  I wrote solutions for all the common pitfalls and complex cases in the PDO tag wiki  Nevertheless  everyone talking about extensions always missing the 2 important facts about Mysqli and PDO    Prepared statement isn t a silver bullet  There are dynamical identifiers which cannot be bound using prepared statements  There are dynamical queries with an unknown number of parameters which makes query building a difficult task  Neither mysqli   nor PDO functions should have appeared in the application code  There ought to be an abstraction layer between them and application code  which will do all the dirty job of binding  looping  error handling  etc  inside  making application code DRY and clean  Especially for the complex cases like dynamical query building    So  just switching to PDO or mysqli is not enough  One has to use an ORM  or a query builder  or whatever database abstraction class instead of calling raw API functions in their code  And contrary - if you have an abstraction layer between your application code and mysql API - it doesn t actually matter which engine is used  You can use mysql ext until it goes deprecated and then easily rewrite your abstraction class to another engine  having all the application code intact   Here are some examples based on my safemysql class to show how such an abstraction class ought to be    city ids   array 1 2 3    cities      db- gt getCol  SELECT name FROM cities WHERE is IN  a     city ids     Compare this one single line with amount of code you will need with PDO  Then compare with crazy amount of code you will need with raw Mysqli prepared statements  Note that error handling  profiling  query logging already built in and running    insert   array  name    gt   John    surname    gt   O Hara     db- gt query  INSERT INTO users SET  u    insert     Compare it with usual PDO inserts  when every single field name being repeated six to ten times - in all these numerous named placeholders  bindings  and query definitions   Another example    data    db- gt getAll  SELECT   FROM goods ORDER BY  n     GET  order       You can hardly find an example for PDO to handle such practical case  And it will be too wordy and most likely unsafe       So  once more - it is not just raw driver should be your concern but abstraction class  useful not only for silly examples from beginner s manual but to solve whatever real-life problems

User · Answer

Because  amongst other reasons  it s much harder to ensure the input data is sanitized  If you use parametrized queries  as one does with PDO or mysqli you can entirely avoid the risk   As an example  someone could use  enhzflep   drop table users  as a username  The old functions will allow executing multiple statements per query  so something like that nasty bugger can delete a whole table   If one were to use PDO of mysqli  the user-name would end-up being  enhzflep   drop table users    See bobby-tables com

User · Answer

It s possible to define almost all mysql   functions using mysqli or PDO  Just include them on top of your old PHP application  and it will work on PHP7  My solution here    lt  php  define  MYSQL LINK    dbl     GLOBALS MYSQL LINK    null   function mysql link  link null        return   link     null     GLOBALS MYSQL LINK     link     function mysql connect  host   user   pass         GLOBALS MYSQL LINK    mysqli connect  host   user   pass       return  GLOBALS MYSQL LINK      function mysql pconnect  host   user   pass        return mysql connect  host   user   pass      function mysql select db  db   link null         link   mysql link  link       return mysqli select db  link   db      function mysql close  link null         link   mysql link  link       return mysqli close  link      function mysql error  link null         link   mysql link  link       return mysqli error  link      function mysql errno  link null         link   mysql link  link       return mysqli errno  link      function mysql ping  link null         link   mysql link  link       return mysqli ping  link      function mysql stat  link null         link   mysql link  link       return mysqli stat  link      function mysql affected rows  link null         link   mysql link  link       return mysqli affected rows  link      function mysql client encoding  link null         link   mysql link  link       return mysqli character set name  link      function mysql thread id  link null         link   mysql link  link       return mysqli thread id  link      function mysql escape string  string        return mysql real escape string  string      function mysql real escape string  string   link null         link   mysql link  link       return mysqli real escape string  link   string      function mysql query  sql   link null         link   mysql link  link       return mysqli query  link   sql      function mysql unbuffered query  sql   link null         link   mysql link  link       return mysqli query  link   sql  MYSQLI USE RESULT      function mysql set charset  charset   link null        link   mysql link  link       return mysqli set charset  link   charset      function mysql get host info  link null         link   mysql link  link       return mysqli get host info  link      function mysql get proto info  link null         link   mysql link  link       return mysqli get proto info  link     function mysql get server info  link null         link   mysql link  link       return mysqli get server info  link      function mysql info  link null         link   mysql link  link       return mysqli info  link      function mysql get client info          link   mysql link        return mysqli get client info  link      function mysql create db  db   link null         link   mysql link  link        db   str replace          mysqli real escape string  link   db        return mysqli query  link   CREATE DATABASE   db        function mysql drop db  db   link null         link   mysql link  link        db   str replace          mysqli real escape string  link   db        return mysqli query  link   DROP DATABASE   db        function mysql list dbs  link null         link   mysql link  link       return mysqli query  link   SHOW DATABASES       function mysql list fields  db   table   link null         link   mysql link  link        db   str replace          mysqli real escape string  link   db         table   str replace          mysqli real escape string  link   table        return mysqli query  link   SHOW COLUMNS FROM   db    table        function mysql list tables  db   link null         link   mysql link  link        db   str replace          mysqli real escape string  link   db        return mysqli query  link   SHOW TABLES FROM   db        function mysql db query  db   sql   link null         link   mysql link  link       mysqli select db  link   db       return mysqli query  link   sql      function mysql fetch row  qlink        return mysqli fetch row  qlink      function mysql fetch assoc  qlink        return mysqli fetch assoc  qlink      function mysql fetch array  qlink   result MYSQLI BOTH        return mysqli fetch array  qlink   result      function mysql fetch lengths  qlink        return mysqli fetch lengths  qlink      function mysql insert id  qlink        return mysqli insert id  qlink      function mysql num rows  qlink        return mysqli num rows  qlink      function mysql num fields  qlink        return mysqli num fields  qlink      function mysql data seek  qlink   row        return mysqli data seek  qlink   row      function mysql field seek  qlink   offset        return mysqli field seek  qlink   offset      function mysql fetch object  qlink   class  stdClass   array  params null        return   params     null            mysqli fetch object  qlink   class            mysqli fetch object  qlink   class   params      function mysql db name  qlink   row   field  Database         mysqli data seek  qlink   row        db   mysqli fetch assoc  qlink       return  db  field      function mysql fetch field  qlink   offset null        if   offset     null          mysqli field seek  qlink   offset       return mysqli fetch field  qlink      function mysql result  qlink   offset   field 0        if   offset     null          mysqli field seek  qlink   offset        row   mysqli fetch array  qlink       return   is array  row      isset  row  field              false            row  field      function mysql field len  qlink   offset         field   mysqli fetch field direct  qlink   offset       return is object  field     field- gt length   false     function mysql field name  qlink   offset         field   mysqli fetch field direct  qlink   offset       if   is object  field           return false      return empty  field- gt orgname     field- gt name    field- gt orgname     function mysql field table  qlink   offset         field   mysqli fetch field direct  qlink   offset       if   is object  field           return false      return empty  field- gt orgtable     field- gt table    field- gt orgtable     function mysql field type  qlink   offset         field   mysqli fetch field direct  qlink   offset       return is object  field     field- gt type   false     function mysql free result  qlink        try           mysqli free result  qlink         catch  Exception  e            return false            return true

User · Answer

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

User · Answer

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 StackOverflow 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 gt  create table users      - gt  id int 2  primary key auto increment      - gt  userid tinytext      - gt  pass tinytext   Query OK  0 rows affected  0 05 sec   mysql gt  insert into users values null   Fluffeh    mypass    Query OK  1 row affected  0 04 sec   mysql gt  create user  prepared   localhost  identified by  example   Query OK  0 rows affected  0 01 sec   mysql gt  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     lt  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   lt br gt  lt br gt         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     lt br gt             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  lt br gt              else               echo  You could not be verified  Please try again    lt br gt              mysql close  link      gt    lt form name  exploited  method  post  gt      User   lt input type  text  name  user  gt  lt br gt      Pass   lt input type  text  name  pass  gt  lt br gt       lt input type  submit  gt   lt  form gt    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    lt  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- gt prepare  sql  array PDO  ATTR CURSOR   gt  PDO  CURSOR FWDONLY        if  myPDO- gt execute array   user    gt   user    password    gt   pass                  while  row  myPDO- gt fetch PDO  FETCH ASSOC                         echo  My id is    row  id     and my username is    row  userid     and lastly  my password is    row  pass     lt br gt                 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  lt br gt              else               echo  You could not be verified  Please try again    lt br gt             gt    lt form name  exploited  method  post  gt      User   lt input type  text  name  user  gt  lt br gt      Pass   lt input type  text  name  pass  gt  lt br gt       lt input type  submit  gt   lt  form gt    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

User · Answer

PHP offers three different APIs to connect to MySQL  These are the mysql removed as of PHP 7   mysqli  and PDO extensions   The mysql   functions used to be very popular  but their use is not encouraged anymore  The documentation team is discussing the database security situation  and educating users to move away from the commonly used ext mysql extension is part of this  check php internals  deprecating ext mysql    And the later PHP developer team has taken the decision to generate E DEPRECATED errors when users connect to MySQL  whether through mysql connect    mysql pconnect   or the implicit connection functionality built into ext mysql   ext mysql was officially deprecated as of PHP 5 5 and has been removed as of PHP 7   See the Red Box   When you go on any mysql   function manual page  you see a red box  explaining it should not be used anymore   Why    Moving away from ext mysql is not only about security  but also about having access to all the features of the MySQL database   ext mysql was built for MySQL 3 23 and only got very few additions since then while mostly keeping compatibility with this old version which makes the code a bit harder to maintain  Missing features that is not supported by ext mysql include   from PHP manual     Stored procedures  can t handle multiple result sets  Prepared statements Encryption  SSL  Compression Full Charset support   Reason to not use mysql   function    Not under active development Removed as of PHP 7 Lacks an OO interface Doesn t support non-blocking  asynchronous queries Doesn t support prepared statements or parameterized queries Doesn t support stored procedures Doesn t support multiple statements Doesn t support transactions Doesn t support all of the functionality in MySQL 5 1   Above point quoted from Quentin s answer  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   See the comparison of SQL extensions     Suppressing deprecation warnings  While code is being converted to MySQLi PDO  E DEPRECATED errors can be suppressed by setting error reporting in php ini to exclude E DEPRECATED   error reporting   E ALL   E DEPRECATED   Note that this will also hide other deprecation warnings  which  however  may be for things other than MySQL   from PHP manual   The article PDO vs  MySQLi  Which Should You Use  by Dejan Marjanovic will help you to choose   And a better way is PDO  and I am now writing a simple PDO tutorial     A simple and short PDO tutorial    Q  First question in my mind was  what is  PDO    A     PDO     PHP Data Objects     is a database access layer providing a uniform method of access to multiple databases              Connecting to MySQL   With mysql   function or we can say it the old way  deprecated in PHP 5 5 and above    link   mysql connect  localhost    user    pass    mysql select db  testdb    link   mysql set charset  UTF-8    link     With PDO  All you need to do is create a new PDO object  The constructor accepts parameters for specifying the database source PDO s constructor mostly takes four parameters which are DSN  data source name  and  optionally username  password   Here I think you are familiar with all except DSN  this is new in PDO  A DSN is basically a string of options that tell PDO which driver to use  and connection details  For further reference  check PDO MySQL DSN    db   new PDO  mysql host localhost dbname testdb charset utf8    username    password      Note  you can also use charset UTF-8  but sometimes it causes an error  so it s better to use utf8   If there is any connection error  it will throw a PDOException object that can be caught to handle Exception further   Good read  Connections and Connection management      You can also pass in several driver options as an array to the fourth parameter  I recommend passing the parameter which puts PDO into exception mode  Because some PDO drivers don t support native prepared statements  so PDO performs emulation of the prepare  It also lets you manually enable this emulation  To use the native server-side prepared statements  you should explicitly set it false   The other is to turn off prepare emulation which is enabled in the MySQL driver by default  but prepare emulation should be turned off to use PDO safely    I will later explain why prepare emulation should be turned off  To find reason please check this post   It is only usable if you are using an old version of MySQL which I do not recommended   Below is an example of how you can do it    db   new PDO  mysql host localhost dbname testdb charset UTF-8                   username                   password                 array PDO  ATTR EMULATE PREPARES   gt  false                PDO  ATTR ERRMODE   gt  PDO  ERRMODE EXCEPTION      Can we set attributes after PDO construction   Yes  we can also set some attributes after PDO construction with the setAttribute method    db   new PDO  mysql host localhost dbname testdb charset UTF-8                   username                   password     db- gt setAttribute PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION    db- gt setAttribute PDO  ATTR EMULATE PREPARES  false     Error Handling     Error handling is much easier in PDO than mysql     A common practice when using mysql   is     Connected to MySQL  result   mysql query  SELECT   FROM table    link  or die mysql error  link      OR die   is not a good way to handle the error since we can not handle the thing in die  It will just end the script abruptly and then echo the error to the screen which you usually do NOT want to show to your end users  and let bloody hackers discover your schema  Alternately  the return values of mysql   functions can often be used in conjunction with mysql error   to handle errors   PDO offers a better solution  exceptions  Anything we do with PDO should be wrapped in a try-catch block  We can force PDO into one of three error modes by setting the error mode attribute  Three error handling modes are below    PDO  ERRMODE SILENT  It s just setting error codes and acts pretty much the same as mysql   where you must check each result and then look at  db- gt errorInfo    to get the error details  PDO  ERRMODE WARNING Raise E WARNING   Run-time warnings  non-fatal errors   Execution of the script is not halted   PDO  ERRMODE EXCEPTION  Throw exceptions  It represents an error raised by PDO  You should not throw a PDOException from your own code  See Exceptions for more information about exceptions in PHP  It acts very much like or die mysql error      when it isn t caught  But unlike or die    the PDOException can be caught and handled gracefully if you choose to do so    Good read    Errors and error handling    The PDOException class    Exceptions      Like    stmt- gt setAttribute  PDO  ATTR ERRMODE  PDO  ERRMODE SILENT     stmt- gt setAttribute  PDO  ATTR ERRMODE  PDO  ERRMODE WARNING     stmt- gt setAttribute  PDO  ATTR ERRMODE  PDO  ERRMODE EXCEPTION      And you can wrap it in try-catch  like below   try         Connect as appropriate as above      db- gt query  hi      Invalid query     catch  PDOException  ex        echo  An Error occured      User friendly message message you want to show to user     some logging function  ex- gt getMessage         You do not have to handle with try-catch right now  You can catch it at any time appropriate  but I strongly recommend you to use try-catch  Also it may make more sense to catch it at outside the function that calls the PDO stuff   function data fun  db         stmt    db- gt query  SELECT   FROM table        return  stmt- gt fetchAll PDO  FETCH ASSOC        Then later try       data fun  db     catch PDOException  ex          Here you can handle error and show message perform action you want      Also  you can handle by or die   or we can say like mysql    but it will be really varied  You can hide the dangerous error messages in production by turning display errors off and just reading your error log   Now  after reading all the things above  you are probably thinking  what the heck is that when I just want to start leaning simple SELECT  INSERT  UPDATE  or DELETE statements  Don t worry  here we go     Selecting Data    So what you are doing in mysql   is    lt  php  result   mysql query  SELECT   from table   or die mysql error       num rows   mysql num rows  result    while  row   mysql fetch assoc  result         echo  row  field1        Now in PDO  you can do this like    lt  php  stmt    db- gt query  SELECT   FROM table     while  row    stmt- gt fetch PDO  FETCH ASSOC         echo  row  field1        Or   lt  php  stmt    db- gt query  SELECT   FROM table     results    stmt- gt fetchAll PDO  FETCH ASSOC      Use  results   Note  If you are using the method like below  query     this method returns a PDOStatement object  So if you want to fetch the result  use it like above    lt  php foreach  db- gt query  SELECT   FROM table   as  row        echo  row  field1        In PDO Data  it is obtained via the - gt fetch    a method of your statement handle  Before calling fetch  the best approach would be telling PDO how you   d like the data to be fetched  In the below section I am explaining this   Fetch Modes  Note the use of PDO  FETCH ASSOC in the fetch   and fetchAll   code above  This tells PDO to return the rows as an associative array with the field names as keys  There are many other fetch modes too which I will explain one by one   First of all  I explain how to select fetch mode     stmt- gt fetch PDO  FETCH ASSOC    In the above  I have been using fetch    You can also use    PDOStatement  fetchAll   - Returns an array containing all of the result set rows PDOStatement  fetchColumn   - Returns a single column from the next row of a result set PDOStatement  fetchObject   - Fetches the next row and returns it as an object  PDOStatement  setFetchMode   - Set the default fetch mode for this statement   Now I come to fetch mode    PDO  FETCH ASSOC  returns an array indexed by column name as returned in your result set PDO  FETCH BOTH  default   returns an array indexed by both column name and 0-indexed column number as returned in your result set   There are even more choices  Read about them all in PDOStatement Fetch documentation    Getting the row count   Instead of using mysql num rows to get the number of returned rows  you can get a PDOStatement and do rowCount    like    lt  php  stmt    db- gt query  SELECT   FROM table     row count    stmt- gt rowCount    echo  row count   rows selected     Getting the Last Inserted ID   lt  php  result    db- gt exec  INSERT INTO table firstname  lastname  VAULES  John    Doe       insertId    db- gt lastInsertId        Insert and Update or Delete statements    What we are doing in mysql   function is    lt  php  results   mysql query  UPDATE table SET field  value    or die mysql error     echo mysql affected rows  result     And in pdo  this same thing can be done by    lt  php  affected rows    db- gt exec  UPDATE table SET field  value     echo  affected rows    In the above query PDO  exec execute an SQL statement and returns the number of affected rows   Insert and delete will be covered later   The above method is only useful when you are not using variable in query  But when you need to use a variable in a query  do not ever ever try like the above and there for  prepared statement or parameterized statement is     Prepared Statements  Q  What is a prepared statement and why do I need them  A  A prepared statement is a pre-compiled SQL statement that can be executed multiple times by sending only the data to the server   The typical workflow of using a prepared statement is as follows  quoted from Wikipedia three 3 point     Prepare  The statement template is created by the application and sent to the database management system  DBMS   Certain values are left unspecified  called parameters  placeholders or bind variables  labelled   below    INSERT INTO PRODUCT  name  price  VALUES        The DBMS parses  compiles  and performs query optimization on the statement template  and stores the result without executing it  Execute  At a later time  the application supplies  or binds  values for the parameters  and the DBMS executes the statement  possibly returning a result   The application may execute the statement as many times as it wants with different values  In this example  it might supply  Bread  for the first parameter and 1 00 for the second parameter    You can use a prepared statement by including placeholders in your SQL  There are basically three ones without placeholders  don t try this with variable its above one   one with unnamed placeholders  and one with named placeholders   Q  So now  what are named placeholders and how do I use them  A  Named placeholders  Use descriptive names preceded by a colon  instead of question marks  We don t care about position order of value in name place holder     stmt- gt bindParam   bla    bla     bindParam parameter variable data type length driver options   You can also bind using an execute array as well    lt  php  stmt    db- gt prepare  SELECT   FROM table WHERE id  id AND name  name     stmt- gt execute array   name    gt   name    id    gt   id     rows    stmt- gt fetchAll PDO  FETCH ASSOC     Another nice feature for OOP friends is that named placeholders have the ability to insert objects directly into your database  assuming the properties match the named fields  For example   class person       public  name      public  add      function   construct  a  b             this- gt name    a           this- gt add    b            demo   new person  john   29 bla district     stmt    db- gt prepare  INSERT INTO table  name  add  value   name   add      stmt- gt execute  array  demo     Q  So now  what are unnamed placeholders and how do I use them  A  Let s have an example    lt  php  stmt    db- gt prepare  INSERT INTO folks  name  add  values            stmt- gt bindValue 1   name  PDO  PARAM STR    stmt- gt bindValue 2   add  PDO  PARAM STR    stmt- gt execute      and   stmt    db- gt prepare  INSERT INTO folks  name  add  values            stmt- gt execute array  john    29 bla district       In the above  you can see those   instead of a name like in a name place holder  Now in the first example  we assign variables to the various placeholders   stmt- gt bindValue 1   name  PDO  PARAM STR     Then  we assign values to those placeholders and execute the statement  In the second example  the first array element goes to the first   and the second to the second     NOTE  In unnamed placeholders we must take care of the proper order of the elements in the array that we are passing to the PDOStatement  execute   method     SELECT  INSERT  UPDATE  DELETE prepared queries   SELECT    stmt    db- gt prepare  SELECT   FROM table WHERE id  id AND name  name     stmt- gt execute array   name    gt   name    id    gt   id     rows    stmt- gt fetchAll PDO  FETCH ASSOC    INSERT    stmt    db- gt prepare  INSERT INTO table field1 field2  VALUES  field1  field2      stmt- gt execute array   field1    gt   field1    field2    gt   field2     affected rows    stmt- gt rowCount     DELETE    stmt    db- gt prepare  DELETE FROM table WHERE id  id     stmt- gt bindValue   id    id  PDO  PARAM STR    stmt- gt execute     affected rows    stmt- gt rowCount     UPDATE    stmt    db- gt prepare  UPDATE table SET name   WHERE id       stmt- gt execute array  name   id     affected rows    stmt- gt rowCount         NOTE   However PDO and or MySQLi are not completely safe  Check the answer Are PDO prepared statements sufficient to prevent SQL injection  by ircmaxell  Also  I am quoting some part from his answer    pdo- gt setAttribute PDO  ATTR EMULATE PREPARES  false    pdo- gt query  SET NAMES GBK     stmt    pdo- gt prepare  SELECT   FROM test WHERE name     LIMIT 1     stmt- gt execute array chr 0xbf    chr 0x27      OR 1 1

User · Answer

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 does not exist in any supported version of PHP  If you are using a version of PHP which supports it  you are using a version which doesn t get security problems fixed   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  Any of the new functionality in MySQL 5 1 or later    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   See the comparison of SQL extensions

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

Ease of use

Equivalent pdo_* functions in place of mysql_*

Bound parameters can be easy to use

Fix or remove any oldschool `sanitize()` function

How prepared statements differ

Hybrid PDO use

Other options

Examples related to php

Examples related to mysql

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

Ease of use

Equivalent pdo_* functions in place of mysql_*

Bound parameters can be easy to use

Fix or remove any oldschool sanitize() function

How prepared statements differ

Hybrid PDO use

Other options

Examples related to php

Examples related to mysql

Fix or remove any oldschool `sanitize()` function