Should I use mysql, mysqli or PDO?

..oO(macca)

>What should I be using for general MySQL database access? I've been
using the traditional mysql extension for ages, but I'm trying to
update my style to a more OOP paradigm.
I've used PDO briefly but I've not used the mysqli extension yet.
I've read a bit about it though, seems good and more OOP orientated
(for the most part). But PDO seems more generic and transferable.

Use PDO if available.

Micha

macca wrote:

Hi,

What should I be using for general MySQL database access? I've been
using the traditional mysql extension for ages, but I'm trying to
update my style to a more OOP paradigm.
I've used PDO briefly but I've not used the mysqli extension yet.
I've read a bit about it though, seems good and more OOP orientated
(for the most part). But PDO seems more generic and transferable.

Any comments?
TIA

If I'm going to be using only mysql, I use the mysqli classes. I like
the way they're designed and it has less overhead than PDO.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

On Nov 4, 1:51 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:

macca wrote:

Hi,

What should I be using for general MySQL database access? I've been
using the traditional mysql extension for ages, but I'm trying to
update my style to a more OOP paradigm.

I've used PDO briefly but I've not used the mysqli extension yet.
I've read a bit about it though, seems good and more OOP orientated
(for the most part). But PDO seems more generic and transferable.

Any comments?

TIA

If I'm going to be using only mysql, I use the mysqli classes. I like
the way they're designed and it has less overhead than PDO.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck...@attglobal.net
==================

I've written my own database class using PHP's mysql functions, rather
than using mysqli, and I've added functions to automatically get,
insert and update. In the end, it's a lot faster than the built-in
mysqli, and it didn't take long to write.

On Mon, 05 Nov 2007 09:20:08 +0100, <ja***@jgoode.co.ukwrote:

On Nov 4, 1:51 pm, Jerry Stuckle <jstuck...@attglobal.netwrote:

>macca wrote:

Hi,

What should I be using for general MySQL database access? I've been
using the traditional mysql extension for ages, but I'm trying to
update my style to a more OOP paradigm.

I've used PDO briefly but I've not used the mysqli extension yet.
I've read a bit about it though, seems good and more OOP orientated
(for the most part). But PDO seems more generic and transferable.

Any comments?

TIA

If I'm going to be using only mysql, I use the mysqli classes. I like
the way they're designed and it has less overhead than PDO.

I've written my own database class using PHP's mysql functions, rather
than using mysqli, and I've added functions to automatically get,
insert and update. In the end, it's a lot faster than the built-in
mysqli, and it didn't take long to write.

There's one major advantage of mysqli though: real prepared statements.
PHP doesn't know everything about the MySQL server, so escaping string can
be tricky business (especially with 'broken' Unicode, there's a very slim
possibility a quote will appear where there was none). Prepared statements
free you from that headache.
--
Rik Wasmus

..oO(ja***@jgoode.co.uk)

>I've written my own database class using PHP's mysql functions, rather
than using mysqli, and I've added functions to automatically get,
insert and update.

The old MySQL extension misses some really important features like
prepared statements and native transaction support.

>In the end, it's a lot faster than the built-in
mysqli, and it didn't take long to write.

Did you test that? Your wrapper class is written in PHP, while the
MySQLi and PDO extensions are written in C and directly call the MySQL
API. Of course there will be some overhead because of the advanced
features (especially in PDO), but I doubt that this makes much of a
difference. What really counts are the queries sent to the DB and the
table structure, not the used interface (IMHO).

Micha

Greetings, Rik Wasmus.
In reply to Your message dated Monday, November 5, 2007, 11:42:54,

PHP doesn't know everything about the MySQL server, so escaping string can
be tricky business (especially with 'broken' Unicode, there's a very slim
possibility a quote will appear where there was none). Prepared statements
free you from that headache.

Sorry, but... what mysql_real_escape_string function does then?
--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

On Nov 6, 6:37 am, AnrDaemon <anrdae...@freemail.ruwrote:

Greetings, Rik Wasmus.
In reply to Your message dated Monday, November 5, 2007, 11:42:54,

PHP doesn't know everything about the MySQL server, so escaping string can
be tricky business (especially with 'broken' Unicode, there's a very slim
possibility a quote will appear where there was none). Prepared statements
free you from that headache.

Sorry, but... what mysql_real_escape_string function does then?

--
Sincerely Yours, AnrDaemon <anrdae...@freemail.ru>

the *_real_escape_string family get the encoding they're supposed to
escape from mysql while connecting, if you happen to set mysql to
another encoding (and in some edge cases just in php) you might not
get the string you expected in mysql. That's what Wasmus was talking
about, when he mentioned there's a chance of a quote appearing where
you didn't expect it.

A user might put a character that's supposed to be a in cp1251, but
is a ' in some Uganda encoding. You happen to be in uganda and you
happen to not use true UTF, so you do some encoding switching. Worst
case scenario - maybe a table will be dropped. Hackers on the other
hand try huge amounts of possible sql injections. If there's a weak
spot, they're bound to find it sooner or later.

If you're keen on using the mysql extension, make suer everything you
do is true unicode, but there's still the chance you happen to forget
to escape something, somewhere, somethime.

Greetings, NoDude.
In reply to Your message dated Tuesday, November 6, 2007, 12:25:08,

PHP doesn't know everything about the MySQL server, so escaping string can
be tricky business (especially with 'broken' Unicode, there's a very slim
possibility a quote will appear where there was none). Prepared statements
free you from that headache.

Sorry, but... what mysql_real_escape_string function does then?

the *_real_escape_string family get the encoding they're supposed to
escape from mysql while connecting, if you happen to set mysql to
another encoding (and in some edge cases just in php) you might not
get the string you expected in mysql. That's what Wasmus was talking
about, when he mentioned there's a chance of a quote appearing where
you didn't expect it.

A user might put a character that's supposed to be a in cp1251, but
is a ' in some Uganda encoding. You happen to be in uganda and you
happen to not use true UTF, so you do some encoding switching. Worst
case scenario - maybe a table will be dropped. Hackers on the other
hand try huge amounts of possible sql injections. If there's a weak
spot, they're bound to find it sooner or later.

If you're keen on using the mysql extension, make suer everything you
do is true unicode, but there's still the chance you happen to forget
to escape something, somewhere, somethime.

Example? I can't understand what You talking about.

If I working with *_real_escape_string, it is for sure escaping characters
which would cause damage to SQL statement in current SQL encoding.
Not related to encoding PHP uses. It is just thing from different world.

So then, if someone supplied a string in any encoding, it is only byte array
while passed to escaping function.
--
Sincerely Yours, AnrDaemon <an*******@freemail.ru>

AnrDaemon wrote:

Greetings, NoDude.
In reply to Your message dated Tuesday, November 6, 2007, 12:25:08,

>>>PHP doesn't know everything about the MySQL server, so escaping string can
be tricky business (especially with 'broken' Unicode, there's a very slim
possibility a quote will appear where there was none). Prepared statements
free you from that headache.
Sorry, but... what mysql_real_escape_string function does then?

>the *_real_escape_string family get the encoding they're supposed to
escape from mysql while connecting, if you happen to set mysql to
another encoding (and in some edge cases just in php) you might not
get the string you expected in mysql. That's what Wasmus was talking
about, when he mentioned there's a chance of a quote appearing where
you didn't expect it.

>A user might put a character that's supposed to be a in cp1251, but
is a ' in some Uganda encoding. You happen to be in uganda and you
happen to not use true UTF, so you do some encoding switching. Worst
case scenario - maybe a table will be dropped. Hackers on the other
hand try huge amounts of possible sql injections. If there's a weak
spot, they're bound to find it sooner or later.

>If you're keen on using the mysql extension, make suer everything you
do is true unicode, but there's still the chance you happen to forget
to escape something, somewhere, somethime.

Example? I can't understand what You talking about.

If I working with *_real_escape_string, it is for sure escaping characters
which would cause damage to SQL statement in current SQL encoding.
Not related to encoding PHP uses. It is just thing from different world.

So then, if someone supplied a string in any encoding, it is only byte array
while passed to escaping function.

That is true. mysql_real_escape_string() should prevent problems with
strings using the current encoding.

Now, it may not be the encoding you *want* - but there shouldn't be any
problems inserting or updating data using it. At least barring any bugs
in the function, of course :-)

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================

On Nov 5, 8:48 am, Michael Fesser <neti...@gmx.dewrote:

.oO(ja...@jgoode.co.uk)

I've written my own database class using PHP's mysql functions, rather
than using mysqli, and I've added functions to automatically get,
insert and update.

The old MySQL extension misses some really important features like
prepared statements and native transaction support.

In the end, it's a lot faster than the built-in
mysqli, and it didn't take long to write.

Did you test that? Your wrapper class is written in PHP, while the
MySQLi and PDO extensions are written in C and directly call the MySQL
API. Of course there will be some overhead because of the advanced
features (especially in PDO), but I doubt that this makes much of a
difference. What really counts are the queries sent to the DB and the
table structure, not the used interface (IMHO).

Micha

Just to clarify my previous post; by faster, I meant the process of
writing code is faster, not the speed at which database actions are
performed, for instance, my class includes a 'get' function, and
$array = data::get('blog_entries') would fetch all of the posts in the
table blog_entries.

..oO(ja***@jgoode.co.uk)

>Just to clarify my previous post; by faster, I meant the process of
writing code is faster

OK, now it makes sense.

>not the speed at which database actions are
performed, for instance, my class includes a 'get' function, and
$array = data::get('blog_entries') would fetch all of the posts in the
table blog_entries.

I've similar functions in my own DB class, which is an extension of the
PDO class.

Micha