If we had trained people to simply do

    $sth = $dbh->("INSERT INTO folks (name, addr, city) values (?, ?, ?);");
    $sth->execute(array($name, $addr, $city));

  $stmt = $somePDOObject->prepare("INSERT INTO folks " .
    "(name, addr, city) values (:name, :addr, :city);");
  $stmt->bindValue(":name", $name);
  $stmt->bindValue(":addr", $addr);
  $stmt->bindValue(":city", $city);

    $stmt = $somePDOObject->prepare("INSERT INTO folks " .
    "(name, addr, city) values (:name, :addr, :city);");
    $stmt->execute(
      array(
        ":name" => $name,
        ":addr" => $addr,
        ":city" => $city));

Meh. Prepared statements are useful when you'll use the it multiple times through rebinding, and for one-shots it's easy enough to have a function taking a parameterized query and an array and doing the annoying crap internally.

Then you just write:

    $result = executeSQL($somePDOObject,
        "INSERT INTO folks (name, addr, city) values (:name, :addr, :city);",
        array("name" => $name, "addr" => $addr, "city" => $city);

No, or he would have mentioned it.

It's clearer (especially as the number of parameters increase) and easier to change (in order to e.g. add a new parameter)

Named parameters also make it easier to re-use queries (have a queries file or something) because you're much less apt to immediately break something by changing a query if everything is explicitly bound via names. And you can make the statements as a whole more terse if you'd like; I intentionally wrote it verbosely.

In your example, you have one place where you have position based binding that you could break while reusing or refactoring.

There's also the proclivity for people to want to reuse the same value in multiple places in a query. Name-based binding makes that a lot more readable and straightforward.

Think fast: is everything in the code below correct? I've already forgotten whether I've added an extra ? or left one off, and I just typed this 30 seconds ago.

    $sth = $dbh->("INSERT INTO a_table (lorem, ipsum, dolor, sit, amet, consectetur, adipiscing, elit, donec, malesuada, purus, et, tellus, dignissim, tristique, in, ipsum, neque, ultricies, quis, hendrerit, ac, pulvinar, eget, enim)
    ) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
    $sth->execute(array($lorem, $ipsum, $dolor, $sit, $amet, $consectetur, $adipiscing, $elit, $donec, $malesuada, $purus, $et, $tellus, $dignissim, $tristique, $in, $ipsum, $neque, $ultricies, $quis, $hendrerit, $ac, $pulvinar, $enim, $eget));

also, the named version above can have query length issues I think.

Not to mention, "PHP: The Good Parts". I almost threw that book out when I hit those parts.

In Rails I'd write:

  Folk.create(:name => some_name, :addr => some_addr, :city => some_city)

This is not meant to be a flame post - I'm just asking: Is it the preferred way in PHP to prepare SQL statements yourself?

I don't think RoR vs PHP would be an apples to apples comparison.

Do you not understand why you can't compare Rails (framework) to PHP (language)?

PHP has frameworks with ORM, too.

No 'serious hindrance' involved.

Greetings PHP geeks,

Don't panic! This is not a proposal to add errors or remove this popular extension. \ Not yet anyway, because it's too popular to do that now.

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.

This proposal only deals with education, and requests permission to officially \ convince people to stop using this old extension. This means:

- Add notes that refer to it as deprecated - Recommend and link alternatives - Include examples of alternatives

There are two alternative extensions: pdo_mysql and mysqli, with PDO being the PHP \ way and main focus of future endeavors. Right? Please don't digress into the PDO v2 \ fiasco here.

What this means to ext/mysql:

- Softly deprecate ext/mysql with education (docs) starting today - Not adding E_DEPRECATED errors in 5.4, but revisit for 5.5/6.0 - Add pdo_mysql examples within the ext/mysql docs that mimic the current examples, but occasionally introduce features like prepared statements - Focus energy on cleaning up the pdo_mysql and mysqli documentation - Create a general "The MySQL situation" document that explains the situation

The PHP community has been recommending alternatives for several years now, so \ hopefully this won't be a new concept or shock to most users.

Regards, Philip

Sure nobody is really running any PHP sites on Windows boxes, but they're sure as hell developing on them. And while I get that the core PHP devs couldn't find a Windows machine if their lives depended on it, enabling coders on Windows machines is what brought them to their current size - something they've clearly forgot since http://pecl4win.php.net/ died.[/endrant]

VMWare Player and VirtualBox are free, and it's quite easy to setup an Ubuntu image that mimics your server platform (if you must be untethered)... alternatively put everything in the (private, if need be) cloud.

For my own projects, I would rather work in Windows, with a mild annoyance when testing, than consistently be annoyed by working in either. (I do use a VM for testing before pushing to production, but for quick "is this right" testing, bouncing Django on Windows and eyeballing it is generally good enough to keep pushing forward.)

You can still develop on Windows, just instead of testing on localhost, you'd test on local VM.

I am perfectly happy with this setup, and haven't run across any downsides.

Developers appreciated that significant config changes boiled down to: get all of your local changes into version control, then grab the newest VM image off the network drive.

It's significantly more painful when you get out of that environment. If you have lots of projects, juggling VMs is a pain. They take up lots of space. Spinning them up/down takes minutes. You don't just have one working IP/hostname/Samba mount/etc that's always there. The resource hit from running a VM is a bigger deal on a netbook. Etc..

Hell, that's what I do and my main Desktop does run Linux, but I devel on a junk machine running Apache/PHP, PostgreSQL.

First part is somewhat false. Second part is very true.

Having developed, provided, and commercialized a WAMP server that was started in 2003 (http://www.devside.net/server/webdeveloper) I've gain some knowledge of who uses PHP on Windows.

Everyone. In all kinds of situations. There is absolutely nothing lost between hosting Wordpress on Windows vs. Linux. Security, performance, maintainability, etc.

I've also found that around 2009, half of the Apache, MySQL, and PHP downloads from their official sites where for the Windows builds.

These numbers have only gone up since then.

The Windows platform for PHP is critical to the health of PHP. Loose it, and you've lost half of the developers and about 30% of the production deployments.

please remember in the future to do that better.

MySQL is the "human-readable" name of ext/mysql.

Source: PHP's documentation http://www.php.net/manual/en/mysqli.overview.php

No, I don't think it is because it makes no sense at any level of resolution. MySQL is by a very long shot the most common pairing for PHP data persistence, unless the project is taken over by Microsoft (and drops support for anything but MSSQL) there's no way for such a thing to happen.

It simply is not a sensible interpretation of the headline.

[edit] fixed a typo

"Oh nice, PHP is deprecating mysql." "What's the recommendation, mysqli?"

"MySQL" is often just used to refer to the extension in PHP parlance.

(I'm a gamedev guy, not a webdev guy.)

Instead, they should have introduced the new libraries in a big version bump, and immediately deprecated the old and unsafe libraries. A couple languages / frameworks that are good at doing this is Apple with Cocoa, and Ruby / Rails.

Bonus points if you're not actually in control of the version of PHP your web app runs on.

I remember one guy who was a PHP developer at the company for years. He was really good at it though. However during my apprenticeship I asked him about best practices querying databases and showed him my code. Looking at it he said: "Why the hell would someone escape those values? It's a database, not the Pentagon."

I'm less interested in features (data layer abstraction) and more interested in performance and security concerns.

They both offer the same security (though I believe mysqli offers more rope to hang yourself with, and PDO's API is simpler and smaller).

In the past, mysqli had the performance edge. It's probably still the case, since PDO is db-independent (PHP bundles a dozen of PDO db drivers in the standard distro) whereas mysqli is db-specific. Mysqli will also offer access to more db-specific features if mysql.

PDO has ditched the procedural style entirely (no PHP4 compat), has a smaller API and lets you use the same API for different DBs (across projects, so you'll hit a MySQL and a Postgres db using roughly the same query API).

  mysql_query("INSERT INTO table(field) VALUES('".$_POST['input']."')");

  include('pages/'.$_GET['page']);

After having some fun, we usually sent emails to the administrators just to not receive any answers and to find the hosts still vulnerable months later...

"The call is coming from inside the house!"

I concede of course that this can't work unless there is absolute discipline in a project to quote absolutely all variables, whether you think they are trusted or not.

Am I missing a key point here?

escaped_query="INSERT INTO t (a,b) VALUES ('$!some','$!thing');

The "!" means "escape this variable".

That would be the easiest way to create queries with escaped parameters.

Something like ...

  foreach($_REQUEST as $k=$v) {
    $REQUEST[$k] = mysql_real_escape_string($v);
  }

  $query = "INSERT INTO table VALUES('$REQUEST[email]', '$REQUEST[name]')";

You could also do something like ...

  $query = "INSERT INTO table VALUES('{$e('email')}', '{$e('email')}')";
  $e = 'esc';
  function esc($v) {
    mysql_real_escape_string($v);
  }

old way:

  $sql_email=mysql_real_escape_string($email);
  $sql="UPDATE t WHERE id=123 SET email='$sql_email'";

  $sql="UPDATE t WHERE id=123 SET email='{$e($email)}'";

Besides, I'm sure some well-meaning soul will come up with some API-identical library or another to drop in. Yes, it'll still take time; consider it penance for screwing up.

But as noted by a sibling comment, it's deprecated instead of being removed. You have plenty of time to not fix your sites, I guess.

    $f = array_filter(get_defined_functions()['internal'], create_function('$i', 'return substr(0, 6, $i) === "mysqli_"'));
    foreach ($f as $func) {
      eval ('
          function ' . str_replace("mysqli", "mysql") . ' {
            return call_user_func(' . $func . ', function_get_args());
          }
      ');
    }

I mean, you can all but to mysqli with %s/mysql/mysqli/g if you wanted to be lazy about it. Most the mysql_ functions have mysqli_ equivalents. (But really you should be using the OO MySQLi at least, preferably PDO anyway).

This is a change for future PHP versions. It will in no way affect the version of PHP the sites you refer to run, unless they run said future version, which is impossible. I'd suggest you remind the businesses that own these sites that there would be absolutely no reason for the to upgrade PHP versions if the software they're currently running satisfies them.

I can assure you the idea of shared hosts upgrading too quickly is a non-issue. It's 2011 and there are still shared hosts running PHP 4.

The mysqli extension has been out for 5 years already, if you've got 300 sites that need to be recoded you might want to get on that. It's not difficult to switch from mysql to mysqli -- the API is very similar. But you have a long time; PHP 5.4 won't even deprecate it and it's not even released yet!

I believe mysqli and PDO were released with PHP 5.0. That's July 2004.

That's 7 years ago.

Do you understand written words? Because you don't seem to grasp the concept of deprecation.

> And I don't have time for that. Nor does anyone else. I understand what they're saying, and I understand the need to migrate over time ( i.e. stop using mysql and move to mysqli today ).

Actually, "stop using mysql and move to PDO/mysqli" has been the recommendation since PHP 5.0 was released.

It happened 7 years ago.

You only have yourself to blame.

Oh, and since you don't care for not-broken APIs, it's not like you'll have to migrate your sites to 5.4 (let alone 5.5), you can just stay on 5.3. There's no mandate here.

mysqli even features the real_escape_string abortion

Considering that a) no out-ass money has been offered me, and b) I'm effectively the only person who could even fix bugs in the system, they're still running 4.0.

Most of them are amazingly still in business, and their clients sites are still amazingly vulnerable. And we wonder why PHP has such a bad rep.

The linked email repeated a half dozen times that this would be "soft deprecation", affecting the documentation, not throwing any error in the code.

How, exactly, would that "break" any sites on the Internet?

mysql/ext is so popular that all default installations will start hiding E_DEPRECATED, which means that no one will ever again see depreciated warning even for things that are easy to fix.