#1
  1. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014

    How to (properly) access a MySQL database with PHP


    How to (properly) access a MySQL database with PHP



    Riding a dead horse: The old MySQL extension

    While the mysql_...() functions remain the most popular way of accessing a database in PHP, they're in fact hopelessly outdated. They were superseded by modern database extensions almost a decade ago. As of PHP 5.5, the old MySQL extension is officially deprecated, which means every single function call generates an E_DEPRECATED error. And one of the next PHP versions will finally remove the extension altogether.

    If you're still using the old MySQL extension, the time to switch is now. That doesn't mean you have to rewrite your 1,000,000 lines of legacy code tomorrow. The PHP language developers are well aware that many programmers still haven't heard the “news”, despite all the big red warning signs in the PHP manual. So there will be a certain grace period. But if you're working on a new project, don't use the old extension. It won't last another decade. And if your book or tutorial still teaches this extension, throw it away. It obviously hasn't been updated in the last 10 years, which is a long time in the world of web development.

    The biggest problem of the mysql_...() functions is the total lack of security features. Assembling query strings from SQL snippets and all kinds of input has turned out to be a terrible idea. Many programmers aren't aware that they have to escape the input in order to prevent SQL injections. And the people who do know about escaping often get it wrong. They use the wrong function like addslashes(), or they break the escaping function through the infamous SET NAMES, or they simply forget the escaping once in a while. As a result, we now have tons of security vulnerabilities in PHP application. This has to stop.



    Welcome to the 21st century: PDO and MySQLi

    To overcome the issues of the old MySQl extension and introduce modern database features, two new database extensions were invented: PDO and MySQLi.

    PDO (“PHP Data Objects”) is meant to be a universal database extension for all mainstream database systems, not just MySQL. You can also use it for PostgreSQL, Oracle Database, MS SQL Server, SQLite etc. On the other hand, MySQLi (the “i” stands for “improved”) is the director successor of the old MySQL extension. That is, you can only use it in conjunction with a MySQL database.

    Which of those two extensions you choose is a matter of your requirements and personal preferences. If you're not married to MySQL, then it's probably a good idea to choose PDO as it can be used with other database systems as well. PDO also tends to be more convenient in many aspects. However, there's a configuration pitfall you need to know about (see “Connecting to the database”). And PDO only has an object-oriented interface. If you've never worked with object-oriented programming before, it may be easier to use the classical functions of MySQLi.

    Either way, this should be a pragmatic choice, not a religious one.



    How to switch

    An interesting phenomenon I've noticed is that many people “switch” to MySQLi merely by replacing mysql_...() with mysqli_...() or even mixing both variants. This is missing the point.

    Switching to MySQLi or PDO doesn't magically improve your code. You have to actually use the new features. It's also important to use the new extensions correctly. I've already seen a lot of insecure techniques, bad practices and plain nonsense being taught in PDO or MySQLi tutorials.

    Below, you'll find code examples of how to do common tasks with PDO and MySQLi.



    Security matters

    While you rewrite your database code or start a new project, one aspect you should always have in mind is improving the security of your code.

    PDO and MySQLi both introduce prepared statements as a secure way of passing values to dynamic queries. Make use of this feature and avoid manual escaping whenever possible.

    A prepared statement is a kind of query template with parameters for values. You first send the query template to the database system. It gets parsed and compiled just like a normal query, but it can't be executed yet. Then you assign the actual values to the parameters and again send them to the database system. And finally, you can execute the whole thing like a query.

    The security of this approach comes from strictly separating the query itself from the values that should go into it. There's no risk of attackers injecting SQL code, because the values are actually treated as values and not as a part of the query. The query is predefined and never changes.

    Prepared statements are actually similar to functions. When you define a function, you first specify the code that should be executed. If certain values should be dynamic, you use parameters. And then you call the function, passing values to the parameters.
    Last edited by Jacques1; November 13th, 2013 at 11:33 AM.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014

    Pdo


    PDO


    Connecting to the database

    Establishing a database connection with PDO is a bit more complicated than calling mysql_connect(), because there are several configuration options.

    It generally works like this:

    PHP Code:
    // connect to the database
    $database = new PDO('mysql:host=localhost;dbname=YOUR_DB;charset=utf8''YOUR_USER''YOUR_PASSWORD'$database_options); 
    The first argument is the DSN (“data source name”) string. It contains the basic connection parameters like the database driver, the server to connect to and possibly other parameters like the port or the character set. After this, you specify the database user and the password. And the last argument is a configuration array.

    Configuring PDO is actually very important. By default, PDO doesn't use actual prepared statements. Instead, it merely escapes the values, inserts them into the query string and sends the whole query to the database system. This is a huge security risk and must be turned off.

    It's also a good idea to activate exceptions. This lets you get rid of manual error checking, because PHP will automatically generate an error and stop the code in case something goes wrong. It also allows for sophisticated error handling.

    Last but not least, you can specify whether PDO should return rows as associative arrays, numerical arrays or mixed arrays.

    As a complete example:

    PHP Code:
    // configure PDO:

    $database_options = array(
        
    // important! use actual prepared statements (default: emulate prepared statements)
        
    PDO::ATTR_EMULATE_PREPARES => false
        
    // throw exceptions in case of errors (default: stay silent)
        
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        
    // fetch associative arrays (default: mixed arrays)
        
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    );

    // connect to the database:

    // important! specify the character encoding in the DSN string, don't use SET NAMES
    $database = new PDO('mysql:host=localhost;dbname=YOUR_DB;charset=utf8''YOUR_USER''YOUR_PASSWORD'$database_options); 
    Please note: If you want to define the character encoding of the connection, you have to do it in the DSN string as shown above. Do not use SET NAMES, because this will “silently” change the encoding without notifying PDO. As a result, critical functionalities that rely on the character encoding (like the quote() method) may break. This again is a major security risk.



    Constant queries

    With “constant” I mean that the query is a fixed string and doesn't include any external values. If you need to pass values from PHP to your query, see the next chapter.

    Constant queries are done by calling the query() method. You can then fetch the rows with a foreach loop or use one of the various fetch methods like fetchAll() or fetchColumn().

    PHP Code:
    // constant query: get all blog posts

    $posts_result $database->query('
        SELECT
            post_id
            , content
            , author
            , created_on
        FROM
            blog_posts
        WHERE
            published
        ORDER BY
            created_on DESC
    '
    );

    // fetch blog posts
    foreach ($posts_result as $blog_post) {
         echo 
    html_escape('Post ' $blog_post['post_id'] . ' from ' $blog_post['created_on'] . ' by user ' $blog_post['author'] . ' says: ' $blog_post['content']) . '<br/>';

    Dynamic queries

    Whenever you want to use external values in a query, you should use a prepared statement. This allows you to safely pass the values to the query without risking an SQL injection.

    Using a prepared statement consists of three separate steps:
    • creating the statement
    • passing values to the parameters
    • executing the statement

    PDO allows you to combine the last two steps.

    PHP Code:
    // prepared statement: get blog posts by a certain author

    // create prepared statement
    $posts_stmt $database->prepare('
        SELECT
            post_id
            , content
            , created_on
        FROM
            blog_posts
        WHERE
            published
            AND author = :author
            AND DATE(created_on) = :created_on
        ORDER BY
            created_on DESC
    '
    );

    // execute statement, binding values to the parameters
    $posts_stmt->execute(array(
        
    'author' => $author
        
    'created_on' => $created_on
    ));

    // fetch blog posts
    foreach ($posts_stmt as $blog_post) {
        echo 
    html_escape('Post ' $blog_post['post_id'] . ' from ' $blog_post['created_on'] . ' says: ' $blog_post['content']) . '<br/>';

    This should be more or less self-explanatory. The query parameters are marked with a double colon. Then you assign values to the parameters. And finally you execute the statement. There are other ways of doing this, but the example above is the easiest approach.

    Fetching the result set works just like with a constant query.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014

    MySQLi


    MySQLi


    Connecting to the database

    Nothing special about this:

    PHP Code:
    // connect to the database
    $database = new mysqli('localhost''YOUR_USER''YOUR_PASSWORD''YOUR_DB');

    // set character encoding (not needed if you're fine with the default encoding)
    $database->set_charset('utf8'); 
    There's also a very useful new feature. You can make MySQLi throw an exception in case of an error. This way you no longer have to manually check the return value of every function call. MySQLi will take care of that.

    PHP Code:
    // turn on exceptions in MySQLi driver
    $database_driver = new mysqli_driver();
    $database_driver->report_mode MYSQLI_REPORT_ERROR MYSQLI_REPORT_STRICT;

    // connect to the database
    $database = ... 
    Constant queries

    With “constant” I mean that the query is a fixed string and doesn't include any external values. If you need to pass values from PHP to your query, see the next chapter.

    Constant queries are done by calling the query() method. You can then fetch the rows with a while loop using one of the fetch methods like fetch_assoc():

    PHP Code:
    // constant query: get all blog posts
    $posts_result $database->query('
        SELECT
            post_id
            , content
            , author
            , created_on
        FROM
            blog_posts
        WHERE
            published
        ORDER BY
            created_on DESC
    '
    );

    // fetch blog posts
    while ($blog_post $posts_result->fetch_assoc())
        echo 
    html_escape('Post ' $blog_post['post_id'] . ' from ' $blog_post['created_on'] . ' by user ' $blog_post['author'] . ' says: ' $blog_post['content']) . '<br/>'
    Dynamic queries

    Whenever you want to use external values in a query, you should use a prepared statement. This allows you to safely pass the values to the query without risking an SQL injection.

    Using a prepared statement consists of three separate steps:
    • creating the statement
    • passing values to the parameters
    • executing the statement

    Unfortunately, this is rather cumbersome with MySQLi.

    PHP Code:
    // prepared statement: get blog posts by a certain author at a certain date
    $posts_stmt $database->prepare('
        SELECT
            post_id
            , content
            , created_on
        FROM
            blog_posts
        WHERE
            published
            AND author = ?
            AND DATE(created_on) = ?
        ORDER BY
            created_on DESC
    '
    );

    // bind values to parameters
    $posts_stmt->bind_param('is'$author$created_on);

    // execute statement
    $posts_stmt->execute();

    // assign columns of result set to variables
    $posts_stmt->bind_result($post_id$post_content$post_created_on);

    // fetch rows
    while ($posts_stmt->fetch())
        echo 
    html_escape('Post ' $post_id ' from ' $post_created_on ' says: ' $post_content) . '<br/>'
    You first have to create the query template with question marks representing the parameters. Then you specify the data types of the values with a string containing certain type identifiers: “i” stands for integer, “s” means string. After you've passed the values to the database system, you have to bind the columns of the result set to variables. And then you can finally fetch the rows.

    PDO is certainly more convenient in that matter.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014

    Bad practices


    Bad practices


    Error handling

    In the old MySQL extension, you had to manually check the return value of every function call, because that was the only way to find out if there was an error. This forced you to clutter the database code with lots and lots of if statements.

    PDO and MySQLi make this technique obsolete. You can now turn on exceptions and delegate the error checking to PHP. See “Connecting to the database”.



    Catching all exceptions

    A weird pattern that seems to get copypasted around the Internet and even made it into the PHP manual is to arbitrarily catch exceptions and print the error message on the screen:

    PHP Code:
    // DO NOT USE THIS! It's is a negative example

    try {
        
    $database->do_something();
    } catch (
    PDOException $error) {
        die(
    'Dear users of the Internet, this database error just happened on my server: ' $error->getMessage());

    This is a terrible idea and doesn't make any sense whatsoever.

    First of all, internal error messages are not meant to be shown to the whole world on your public website. They contain technical information for you, the developer. Exposing them will irritate legitimate users and help attackers gather information about your system. So don't do it. Let PHP take care of the error messages. It will send them to the device you've specified in the php.ini. On a production website, error messages go into an error log and must not be printed on the screen. In your local test environment, you may activate display_errors for easier debugging.

    Secondly, the “error handling” above is completely useless, because it does what PHP would do anyway. Stopping the script and generating an error message is exactly the default behavior of exception. So what's the point of the code in the first place?

    As a simple rule: If you can't solve a runtime error, don't touch it. Just let the exception “bubble up”. Either some other code will take of it. Or the exception will abort the script in a controlled manner.



    Fetching result sets

    Another issue is that many people seem to be confused about how to loop through a result set.

    In PDO, a result set is traversable, which means you can use it directly in a foreach loop. You do not need the old while($row = fetch()) pattern. And you certainly shouldn't load the whole result set into an array with fetchAll() if you just wanna go through each individual row.

    So simply use a foreach loop:

    PHP Code:
    foreach ($result_set as $row) {
        
    do_something();

    The result sets of MySQLi are also traversable. However, you can't tell MySQLi what kind of array to fetch (as far as I know), so you'll indeed need the “good” old while loop if you want associative arrays instead of numerical ones.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    44
    Rep Power
    1
    Thanks for sharing.
    I've started out with MYSQL but I am not married to it.
    I want to make a switch. I would like to go PDO but before I start. I'd like to know which of the 2 is faster and better?

    Thanks
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    PDO is much more convenient and supports many different database systems. Unlike MySQLi, it can be used as a universal database interface.

    The performance results vary across benchmarks. Sometimes PDO is slightly faster, sometimes MySQLi. But the difference is irrelevant.

    So I'd definitely go with PDO. But you need to be aware of the emulation problem described above.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    44
    Rep Power
    1
    Thanks... I am off on the highway with PDO
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    44
    Rep Power
    1
    I am a little stuck on a few things...

    I see that there are a few different PDO drivers available for each PHP version.
    I ran
    Code:
    phpinfo();
    on 3 different php versions.

    I have the following options on my US host web server

    PHP Version 5.3.28 has PDO drivers mysql, sqlite, sqlite2
    PHP Version 5.5.10 has PDO drivers mysql, sqlite

    Which PHP version would be the best to run PDO scripts on?

    I also have a Europe host web server which has the PDO drivers dblib, mysql, odbc, sqlite, sqlite2

    Thanks.
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Well, as you can tell from the names, each driver is for a specific database system.

    If you use MySQL, you need the mysql driver (which all three servers have). If you used SQLite, you'd need the sqlite driver etc.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    120
    Rep Power
    1
    Jacques, i know we started out on the wrong foot, but if i could perhaps borrow a second of your time. i saw you talking above about looping through per row, and i just wanted to point out that i have learned through experience you cannot rely on this method as it will not always return results in the correct order. so instead i have used this in php:
    PHP Code:
    $query mysql_query("SELECT * FROM `table`);
    for(
    $i = 0; $i < mysql_num_rows($query);$i++) {
         
    $firstvalue = mysql_result($query$i, "foo");
         
    $secondvalue = mysql_result($query$i, "bar");

    This has been much faster for me in mysql. now that i'm learning mysqli, is there some way i can emulate this functionality? its much faster according to microtime than while row loops.... i work with fairly large datasets as well..
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    44
    Rep Power
    1
    Thanks.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2014
    Posts
    44
    Rep Power
    1
    Is there a special reason why you choose to use MYSQLi rather than PDO?
  24. #13
  25. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Originally Posted by r3wt
    i saw you talking above about looping through per row, and i just wanted to point out that i have learned through experience you cannot rely on this method as it will not always return results in the correct order.
    SQL tables do not have an order unless you specify one with ORDER BY. This is one of the core concepts of SQL.

    Any implicit order you may be seeing is just a technical side effect and may change at any time.



    Originally Posted by r3wt
    so instead i have used this in php:
    PHP Code:
    $query mysql_query("SELECT * FROM `table`);
    for(
    $i = 0; $i < mysql_num_rows($query);$i++) {
         
    $firstvalue = mysql_result($query$i, "foo");
         
    $secondvalue = mysql_result($query$i, "bar");

    This has been much faster for me in mysql.
    I kind of doubt that. How much faster? Microseconds? Milliseconds? Seconds? Did you rule out other factors? The manual explicitly states that this function is much slower when used on big result set, so I'd be very surprised if you got a significant performance boost from it.

    Anyway, this is definitely the last thing to worry about. Performance doesn't come from micro-optimizations like this. If you actually experience slow queries, the chance is 99% that it's due to a missing index or a gross mistake in the application code. I'd start with removing the extremely inefficient selection wildcard.

    If you create a new thread with a concrete problem description, I'm sure we or the MySQL people can help you. Until then, I strongly recommend you stick to the standard fetching methods.



    Originally Posted by r3wt
    i work with fairly large datasets as well..
    Are you talking about millions of rows? Otherwise, it's not large.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  26. #14
  27. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Location
    Adelaide - Australia
    Posts
    77
    Rep Power
    3
    Well,

    It was a stupid question.
    Last edited by slopalong; December 6th, 2014 at 05:16 AM. Reason: No Response

IMN logo majestic logo threadwatch logo seochat tools logo