Results 1 to 4 of 4
  1. #1
    Junior Member
    Join Date
    Apr 2016

    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.

  2. #2

    How to (properly) access a MySQL database with PHP

    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.

  3. #3

    How to (properly) access a MySQL database with PHP

    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.

  4. #4

    How to (properly) access a MySQL database with PHP

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts