What is the difference between MySQL, MySQLi and PDO?

down vote

There are (more than) three popular ways to use MySQL from PHP. (DEPRECATED) The mysql functions are procedural and use manual escaping. MySQLi is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements. PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases. It provides prepared statements, and significant flexibility in how data is returned. PDO_MySQL, is the MySQL for PDO. PDO has been introduced in PHP, and the project aims to make a common API for all the databases access, so in theory you should be able to migrate between RDMS without changing any code (if you don't use specific RDBM function in your queries), also object-oriented. Connection : // PDO 1 $pdo = new PDO("mysql:host=localhost.com;dbname=data", 'username', 'password'); // mysqli, procedural style 1 $mysqli = mysqli_connect('localhost.com','username','password','data'); // mysqli, object oriented style 1 $mysqli = new mysqli('localhost.com','username','password','data'); API Support : Both PDO and MySQLi provides an object-oriented approach, but MySQLi provides a procedural way also like old mysql extension. New users may prefer MySQLi because of procedural interface which is similar to old mysql extension, So the migrating from the old mysql extension is easier. But after mastering in PDO, you can use it with any database you like. Database Support : The main advantage of PDO over MySQLi is its database driver support. PDO supports 12 different drivers and MySQLi supports MySQL only. To get the list of all drivers that PDO supports, use following code: 1 var_dump(PDO::getAvailableDrivers()); I would recommend using PDO with prepared statements. It is a well-designed API and will let you more easily move to another database (including any that supports ODBC) if necessary. PDO Drivers ¶ Table of Contents ¶ CUBRID (PDO) MS SQL Server (PDO) Firebird (PDO) IBM (PDO) Informix (PDO) MySQL (PDO) MS SQL Server (PDO) Oracle (PDO) ODBC and DB2 (PDO) PostgreSQL (PDO) SQLite (PDO) 4D (PDO)

Sql Injection and Its prevention

SQL Injection flaws are introduced when software developers create dynamic database queries that include user supplied input. To avoid SQL injection flaws is simple. Developers need to either: a) stop writing dynamic queries; and/or b) prevent user supplied input which contains malicious SQL from affecting the logic of the executed query.

Primary Defenses:

• Option #1: Use of Prepared Statements (Parameterized Queries)
• Option #2: Use of Stored Procedures
• Option #3: Escaping all User Supplied Input

Additional Defenses:

• Also Enforce: Least Privilege
• Also Perform: White List Input Validation

Defense Option 1: Prepared Statements (with Parameterized Queries)

The use of prepared statements with variable binding (aka parameterized queries) is how all developers should first be taught how to write database queries. They are simple to write, and easier to understand than dynamic queries. Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.

Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker. In the safe example below, if an attacker were to enter the userID of tom' or '1'='1, the parameterized query would not be vulnerable and would instead look for a username which literally matched the entire string tom' or '1'='1.

• PHP – use PDO with strongly typed parameterized queries (using bindParam())

$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

Defense Option 2: Stored Procedures

Stored procedures are not always safe from SQL injection. However, certain standard stored procedure programming constructs have the same effect as the use of parameterized queries when implemented safely* which is the norm for most stored procedure languages. They require the developer to just build SQL statements with parameters which are automatically parameterized unless the developer does something largely out of the norm. The difference between prepared statements and stored procedures is that the SQL code for a stored procedure is defined and stored in the database itself, and then called from the application. Both of these techniques have the same effectiveness in preventing SQL injection so your organization should choose which approach makes the most sense for you.

Defense Option 3: White List Input Validation

Various parts of SQL queries aren't legal locations for the use of bind variables, such as the names of tables or columns, and the sort order indicator (ASC or DESC). In such situations, input validation or query redesign is the most appropriate defense. For the names of tables or columns, ideally those values come from the code, and not from user parameters. But if user parameter values are used to make different for table names and column names, then the parameter values should be mapped to the legal/expected table or column names to make sure unvalidated user input doesn't end up in the query. Please note, this is a symptom of poor design and a full re-write should be considered if time allows. Here is an example of table name validation.

 String tableName;
 switch(PARAM):
   case "Value1": tableName = "fooTable";
                  break;
   case "Value2": tableName = "barTable";
                  break;
     ...
   default      : throw new InputValidationException("unexpected value provided for table name");

The tableName can then be directly appended to the SQL query since it is now known to be one of the legal and expected values for a table name in this query. Keep in mind that generic table validation functions can lead to data loss as table names are used in queries where they are not expected.

For something simple like a sort order, it would be best if the user supplied input is converted to a boolean, and then that boolean is used to select the safe value to append to the query. This is a very standard need in dynamic query creation. For example:

 public String someMethod(boolean sortOrder) {
 
 String SQLquery = "some SQL ... order by Salary " + (sortOrder ? "ASC" : "DESC");
 ...

Any time user input can be converted to a non-String, like a date, numeric, boolean, enumerated type, etc. before it is appended to a query, or used to select a value to append to the query, this ensures it is safe to do so.

Defense Option 4: Escaping All User Supplied Input

This technique should only be used as a last resort, when none of the above are feasible. Input validation is probably a better choice as this methodology is frail compared to other defenses and we cannot guarantee it will prevent all SQL Injection in all situations.

This technique is to escape user input before putting it in a query. It is very database specific in its implementation. Its usually only recommended to retrofit legacy code when implementing input validation isn't cost effective. Applications built from scratch, or applications requiring low risk tolerance should be built or re-written using parameterized queries, stored procedures, or some kind of Object Relational Mapper (ORM) that builds your queries for you.

This technique works like this. Each DBMS supports one or more character escaping schemes specific to certain kinds of queries. If you then escape all user supplied input using the proper escaping scheme for the database you are using, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities.

Least Privilege

To minimize the potential damage of a successful SQL injection attack, you should minimize the privileges assigned to every database account in your environment. Do not assign DBA or admin type access rights to your application accounts. We understand that this is easy, and everything just ‘works’ when you do it this way, but it is very dangerous. Start from the ground up to determine what access rights your application accounts require, rather than trying to figure out what access rights you need to take away. Make sure that accounts that only need read access are only granted read access to the tables they need access to. If an account only needs access to portions of a table, consider creating a view that limits access to that portion of the data and assigning the account access to the view instead, rather than the underlying table. Rarely, if ever, grant create or delete access to database accounts.

Ref : https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet

What technical details should a programmer of a web application consider before making the site public?

The idea here is that most of us should already know most of what is on this list. But there just might be one or two items you haven't really looked into before, don't fully understand, or maybe never even heard of.

Interface and User Experience

• Be aware that browsers implement standards inconsistently and make sure your site works reasonably well across all major browsers. At a minimum test against a recent Geckoengine (Firefox), a WebKit engine (Safari and some mobile browsers), Chrome, your supported IE browsers (take advantage of the Application Compatibility VPC Images), andOpera. Also consider how browsers render your site in different operating systems.
• Consider how people might use the site other than from the major browsers: cell phones, screen readers and search engines, for example. — Some accessibility info: WAI andSection508, Mobile development: MobiForge.
• Staging: How to deploy updates without affecting your users. Have one or more test or staging environments available to implement changes to architecture, code or sweeping content and ensure that they can be deployed in a controlled way without breaking anything. Have an automated way of then deploying approved changes to the live site. This is most effectively implemented in conjunction with the use of a version control system (CVS, Subversion, etc.) and an automated build mechanism (Ant, NAnt, etc.).
• Don't display unfriendly errors directly to the user.
• Don't put users' email addresses in plain text as they will get spammed to death.
• Add the attribute rel="nofollow" to user-generated links to avoid spam.
• Build well-considered limits into your site - This also belongs under Security.
• Learn how to do progressive enhancement.
• Redirect after a POST if that POST was successful, to prevent a refresh from submitting again.
• Don't forget to take accessibility into account. It's always a good idea and in certain circumstances it's a legal requirement. WAI-ARIA and WCAG 2 are good resources in this area.
• Don't make me think

Security

• It's a lot to digest but the OWASP development guide covers Web Site security from top to bottom.
• Know about Injection especially SQL injection and how to prevent it.
• Never trust user input, nor anything else that comes in the request (which includes cookies and hidden form field values!).
• Hash passwords using salt and use different salts for your rows to prevent rainbow attacks. Use a slow hashing algorithm, such as bcrypt (time tested) or scrypt (even stronger, but newer) (1, 2), for storing passwords. (How To Safely Store A Password). TheNIST also approves of PBKDF2 to hash passwords", and it's FIPS approved in .NET (more info here). Avoid using MD5 or SHA family directly.
• Don't try to come up with your own fancy authentication system. It's such an easy thing to get wrong in subtle and untestable ways and you wouldn't even know it until after you're hacked.
• Know the rules for processing credit cards. (See this question as well)
• Use SSL/HTTPS for login and any pages where sensitive data is entered (like credit card info).
• Prevent session hijacking.
• Avoid cross site scripting (XSS).
• Avoid cross site request forgeries (CSRF).
• Avoid Clickjacking.
• Keep your system(s) up to date with the latest patches.
• Make sure your database connection information is secured.
• Keep yourself informed about the latest attack techniques and vulnerabilities affecting your platform.
• Read The Google Browser Security Handbook.
• Read The Web Application Hacker's Handbook.
• Consider The principle of least privilege. Try to run your app server as non-root. (tomcat example)

Performance

• Implement caching if necessary, understand and use HTTP caching properly as well asHTML5 Manifest.
• Optimize images - don't use a 20 KB image for a repeating background.
• Learn how to gzip/deflate content (deflate is better).
• Combine/concatenate multiple stylesheets or multiple script files to reduce number of browser connections and improve gzip ability to compress duplications between files.
• Take a look at the Yahoo Exceptional Performance site, lots of great guidelines, including improving front-end performance and their YSlow tool (requires Firefox, Safari, Chrome or Opera). Also, Google page speed (use with browser extension) is another tool for performance profiling, and it optimizes your images too.
• Use CSS Image Sprites for small related images like toolbars (see the "minimize HTTP requests" point)
• Use SVG image sprites for small related images like toolbars. SVG coloring is bit tricky. You can read about it here.
• Busy web sites should consider splitting components across domains. Specifically...
• Static content (i.e. images, CSS, JavaScript, and generally content that doesn't need access to cookies) should go in a separate domain that does not use cookies, because all cookies for a domain and its subdomains are sent with every request to the domain and its subdomains. One good option here is to use a Content Delivery Network (CDN), but consider the case where that CDN may fail by including alternative CDNs, or local copies that can be served instead.
• Minimize the total number of HTTP requests required for a browser to render the page.
• Utilize Google Closure Compiler for JavaScript and other minification tools.
• Choose a template engine and render/pre-compile it using task-runners like gulp or grunt
• Make sure there’s a favicon.ico file in the root of the site, i.e. /favicon.ico. Browsers will automatically request it, even if the icon isn’t mentioned in the HTML at all. If you don’t have a /favicon.ico, this will result in a lot of 404s, draining your server’s bandwidth.

SEO (Search Engine Optimization)

• Use "search engine friendly" URLs, i.e. use example.com/pages/45-article-title instead of example.com/index.php?page=45
• When using # for dynamic content change the # to #! and then on the server $_REQUEST["_escaped_fragment_"] is what googlebot uses instead of #!. In other words, ./#!page=1 becomes ./?_escaped_fragments_=page=1. Also, for users that may be using FF.b4 or Chromium, history.pushState({"foo":"bar"}, "About", "./?page=1"); Is a great command. So even though the address bar has changed the page does not reload. This allows you to use ? instead of #! to keep dynamic content and also tell the server when you email the link that we are after this page, and the AJAX does not need to make another extra request.
• Don't use links that say "click here". You're wasting an SEO opportunity and it makes things harder for people with screen readers.
• Have an XML sitemap, preferably in the default location /sitemap.xml.
• Use <link rel="canonical" ... /> when you have multiple URLs that point to the same content, this issue can also be addressed from Google Webmaster Tools.
• Use Google Webmaster Tools and Bing Webmaster Tools.
• Install Google Analytics right at the start (or an open source analysis tool like Piwik).
• Know how robots.txt and search engine spiders work.
• Redirect requests (using 301 Moved Permanently) asking for www.example.com to example.com (or the other way round) to prevent splitting the google ranking between both sites.
• Know that there can be badly-behaved spiders out there.
• If you have non-text content look into Google's sitemap extensions for video etc. There is some good information about this in Tim Farley's answer.

Technology

• Understand HTTP and things like GET, POST, sessions, cookies, and what it means to be "stateless".
• Write your XHTML/HTML and CSS according to the W3C specifications and make sure they validate. The goal here is to avoid browser quirks modes and as a bonus make it much easier to work with non-traditional browsers like screen readers and mobile devices.
• Understand how JavaScript is processed in the browser.
• Understand how JavaScript, style sheets, and other resources used by your page are loaded and consider their impact on perceived performance. It is now widely regarded as appropriate to move scripts to the bottom of your pages with exceptions typically being things like analytics apps or HTML5 shims.
• Understand how the JavaScript sandbox works, especially if you intend to use iframes.
• Be aware that JavaScript can and will be disabled, and that AJAX is therefore an extension, not a baseline. Even if most normal users leave it on now, remember thatNoScript is becoming more popular, mobile devices may not work as expected, and Google won't run most of your JavaScript when indexing the site.
• Learn the difference between 301 and 302 redirects (this is also an SEO issue).
• Learn as much as you possibly can about your deployment platform.
• Consider using a Reset Style Sheet or normalize.css.
• Consider JavaScript frameworks (such as jQuery, MooTools, Prototype, Dojo or YUI 3), which will hide a lot of the browser differences when using JavaScript for DOM manipulation.
• Taking perceived performance and JS frameworks together, consider using a service such as the Google Libraries API to load frameworks so that a browser can use a copy of the framework it has already cached rather than downloading a duplicate copy from your site.
• Don't reinvent the wheel. Before doing ANYTHING search for a component or example on how to do it. There is a 99% chance that someone has done it and released an OSS version of the code.
• On the flipside of that, don't start with 20 libraries before you've even decided what your needs are. Particularly on the client-side web where it's almost always ultimately more important to keep things lightweight, fast, and flexible.

Bug fixing

• Understand you'll spend 20% of your time coding and 80% of it maintaining, so code accordingly.
• Set up a good error reporting solution.
• Have a system for people to contact you with suggestions and criticisms.
• Document how the application works for future support staff and people performing maintenance.
• Make frequent backups! (And make sure those backups are functional) Have a restore strategy, not just a backup strategy.
• Use a version control system to store your files, such as Subversion, Mercurial or Git.
• Don't forget to do your Acceptance Testing. Frameworks like Selenium can help. Especially if you fully automate your testing, perhaps by using a Continuous Integration tool, such asJenkins.
• Make sure you have sufficient logging in place using frameworks such as log4j, log4net orlog4r. If something goes wrong on your live site, you'll need a way of finding out what.
• When logging make sure you capture both handled exceptions, and unhandled exceptions. Report/analyse the log output, as it'll show you where the key issues are in your site.

Other

• Implement both server-side and client-side monitoring and analytics (one should be proactive rather than reactive).
• Use services like UserVoice and Intercom (or any other similar tools) to constantly keep in touch with your users.
• Follow Vincent Driessen's Git branching model

Lots of stuff omitted not necessarily because they're not useful answers, but because they're either too detailed, out of scope, or go a bit too far for someone looking to get an overview of the things they should know. Please feel free to edit this as well, I probably missed some stuff or made some mistakes.