

PHP to deprecate MySQL extension - mildweed
http://marc.info/?l=php-internals&m=131031747409271&w=2

======
pak
Yes, yes, yes a million times yes. Every person starting PHP should be taught
PDO with parameterized queries from the get go. The internet (and many
respectable CS courses) are littered with examples using mysql_query() and
string concatenation, half of which train people to create SQLI bugs or don't
explain why escaping is needed; it's too late to undo the damage already done
but at least people new to PHP will hopefully be told now that there is a
better way.

If we had trained people to simply do

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

in 2005, when PDO first came out, uncountable SQLI bugs would have been
avoided, and maybe lulzsec wouldn't be having such a field day in 2011...

~~~
eropple
Even that way is kinda fragile and fugly, though. I always recommend:

    
    
      $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);
    

Slightly more verbose, but it has the upside of being more resilient.

~~~
Groxx
If you go the resilient route with _everything_ you end up with code that
looks like Java code. It's not usually worth it, as when the query changes,
odds are you'll be looking at the parameters, and will notice changes. Not
true for large queries, of course, but for beginners? They're writing things
like the above, where the entire process fits in a single eyefull. Making it
more verbose from the get-go means they're more likely to just concatenate
strings.

~~~
masklinn
> If you go the resilient route with everything you end up with code that
> looks like Java code.

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);

~~~
eropple
This is actually fairly close to what I do in my own code (I actually extend
the PDO object, but same idea). The example I gave was just using the base PDO
library instead.

------
gchucky
I was having a hard time loading it, so in case it goes down, here's the text:

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

------
mscarborough
Would like to see the same for the 'memcache' lib, which is way older and
slower than 'memcached'. The older lib comes up first in most search results
and there's no real way for a new PHP user to know the difference.

~~~
ecaron
The problem is that the newer/better extension, memcached
(<http://php.net/manual/en/book.memcached.php>) relies on libMemcached
(<http://libmemcached.org/>) and they won't release a Windows DLL for it
(Google "libmemcached windows", the ensuing confusion is just the tip of the
iceberg.) Until you can get PHP's memcacheD extension running on Windows
boxes, there's no point to thinking about deprecating the memcache lib.

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]

~~~
r00fus
I honestly don't understand why anyone would develop on Win and release on
Linux, especially for a web-app.

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.

~~~
ecaron
Once you've had a change to run in that kind of environment as a full-time job
for 40+ hours a week, let me know...

~~~
devicenull
I've been developing in a similar environment for over a year now. I have a
linux VM running apache + associated software, with Samba running. I mount my
code as a shared drive, and edit it that way. This means I get the power of
linux + linux tools, but I can still run Windows as my primary OS.

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

------
imajes
@mildweed: the title here is total clickbait. They aren't deprecating anything
right now, it's not MySQL but ext/mysql (one of 3 possible mysql libs).

please remember in the future to do that better.

~~~
masklinn
> They aren't deprecating anything right now, it's not MySQL but ext/mysql

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

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

~~~
imajes
I see your point but you're nitpicking. MySQL is also the name for the
database product, and so it's easy to read this and think that PHP are getting
out of the business supporting MySQL.

~~~
masklinn
> MySQL is also the name for the database product, and so it's easy to read
> this and think that PHP are getting out of the business supporting MySQL.

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.

~~~
cwp
Well, it's not a sensible thing to do, but it's a perfectly reasonable
interpretation of the headline. That's why it's link bait.

[edit] fixed a typo

~~~
palish
This is spot-on, and it's a shame nobody seems to care.

~~~
cwp
Well, somebody cares, if downvotes mean anything.

~~~
dools
If only we could see the fucking points.

------
SeoxyS
This is the kind of mess when you have 5 different libraries to do the same
thing. PHP should never have allowed there to be multiple MySQL, Mamcache,
etc. extensions.

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.

~~~
pavel_lishin
And thus ensured that either you upgrade your version of PHP and instantly
break your application, or that you stick with an older version with potential
security flaws, and never bother updating again?

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

~~~
SeoxyS
Being forever backward-compatible is a curse that prevents innovation and
makes you end up with the mess of a language that PHP is.

~~~
pavel_lishin
I agree, but you don't have to be backward-compatible forever - you can
deprecate features gracefully.

------
pstadler
Definitely a step into the right direction! It's just scary how many bad
developers are passing unescaped values into SQL statements.

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."

~~~
pak
I think people need to be educated that SQLI bugs (errors in escaping) lead to
usability issues as well as security issues. I can't count the number of times
I've searched for something on a clearly handmade site and watched in
amusement as my search query grows backslashes while paging through the
results. Also, single quotes show up in more places than people realize: I've
seen them in proper names, street addresses, uploaded filenames... if those
things break your app or damage data, that _should_ be a tangible concern.

~~~
mildweed
And my favorite place, email addresses. Valid. Rare, but valid.

------
mildweed
This seems like a relevant time to have a discussion on the merits of PDO vs
mysqli. Help all those who need to get off of ext/mysql make an intelligent
decision on where to go next.

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

~~~
masklinn
> 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).

~~~
sequoia
Sorry if this is a silly question but where does MDB2 fit into all this?
<http://pear.php.net/package/MDB2> It looks like it uses mysqli already, is it
superseded by PDO?

~~~
eropple
MDB2 has basically been supplanted by PDO, yes.

------
pstadler
As a person who teaches PHP (and probably other languages too), you should
definitely tell people not to do following mistakes:

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

and not to forget, a truly classic one:

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

When I started to learn PHP there was a contest between the students: Who
brings hosts with vulnerable websites down faster. The trick was to
recusrively include the same page (like index.php for example). The most funny
thing though was to see the explosively rising load average by including
/proc/uptime.

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...

~~~
pavel_lishin
I would consider e-mailing the administrators an e-mail with something along
the lines of "<img src='<http://vulnerable_host/?page=/proc/uptime> />"

"The call is coming from inside the house!"

~~~
pstadler
It was much more fun to send them their own /etc/motd!

------
Almaviva
I've never understood why you can't write secure code using string
concatenation. It seems to me like all it takes is a reliable "quote" method
(with proper escaping of course), and the discipline to use it on _all_
variables that go into a query. The advantage is that queries are sometimes
easier to read and maintain when they're written locally like this, without
having to jump down to another line to see what a placeholder will actually
contain.

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?

------
icode
I wished there was a new PHP syntax that would escape strings before
substituting them. Like this:

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.

~~~
jawns
I've experimented with a variation of this.

Something like ...

    
    
      foreach($_REQUEST as $k=$v) {
        $REQUEST[$k] = mysql_real_escape_string($v);
      }
    
      $query = "INSERT INTO table VALUES('$REQUEST[email]', '$REQUEST[name]')";
    

Problem is, this only works if you don't plan on modifying any of the values
before you stick them in the database.

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);
      }
    

But I think that looks pretty ugly.

~~~
icode
Hey, the {$e('$value')} idea is interesting.

old way:

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

your idea:

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

Im not yet sure, which one I like more.

------
yinrunning
"to do that now"?... What would be a good time to break the majority of
smaller sites on the Internet?...

~~~
whichdan
There are tons and tons of hosts that still haven't upgraded to 5.3 - if the
change comes in 5.5 or 6, it'll easily be 3+ years before it starts to be an
issue.

~~~
pavel_lishin
I know for a fact that my old employer has one client who's still running 4.0,
unless they've decided to pay out the ass to upgrade their spaghetti-coded
system.

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.

~~~
canadiancreed
Reminds me of some places I've worked in my time. Clients made one site and
wouldn't pay for an upgrade, and the folks running the company learned PHP
when version 5 was brand new, and never wanted to upgrade because of a
combination of clients not willing to pay and "seeing no need to upgrade".

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.

------
MostAwesomeDude
I am both amused and horrified by the reluctance of developers to officially
deprecate the extension. One post even suggested introducing
E_NEARLY_DEPRECATED to handle this situation. Why can't people simply say,
straight-up, that it is deprecated, and acknowledge that that means that it
shouldn't be used in new code? It's not that big of a deal, is it?

~~~
courtewing
The problem is that this would be one of the most widely used PHP extensions
being deprecated, so they're just being careful. This is not the normal way
things are deprecated, but education is a good way to start the process of
removing popular functionality from such a popular programming language.

~~~
LawnGnome
Exactly. For something like ereg, it was a lot easier to just deprecate it and
go from there. For mysql, it's such a commonly used extension that a softly,
softly approach is needed, no matter how much it would be nice to get rid of
it immediately.

~~~
ars
If they do actually add E_DEPRECATED to mysql/ext they will probably have a
really hard time every depreciating anything ever again.

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.

