phpMyAdmin MySQL proble,

[Dave Moore]

Hi All,
I'm putting a website together using PHP and a MySQL database. I've been
using phpMyAdmin as it makes updating the DB nice and easy. For development,
I've been using Apache, MySQL and PHP installed on my local machine but now
I'm having problems porting to my hoster's server.

To transfer the database contents, I've exported the structure and data of
my local DB to a .sql file and uploaded this to my hosted DB using
phpMyAdmin on the host. This seems to work, but there are two problems:

1). Firstly, the MySQL PASSWORD() command give different results on the host
than it does on my local machine. For example, using phpMyAdmin on my local
machine, typing:

SELECT PASSWORD ('mypassword')

gives a 16 byte number as my books suggest. However, running the same
command on the server gives a 41 bytes code!!!. Consequently, all my login
functionality does not work!.

2). Secondly, I noticed that phpMyAdmin on the host shows a 'Collation'
column with the value 'latin1_swedish _ci'. This column isn't present when
using phpMyAdmin on my local machine, even though the DB contents show be
identical. So what's that all about??.

Thanks for any comments,
Dave

[Rauch Christian]

Dave Moore schrieb:

Hi All,
I'm putting a website together using PHP and a MySQL database. I've been
using phpMyAdmin as it makes updating the DB nice and easy. For development,
I've been using Apache, MySQL and PHP installed on my local machine but now
I'm having problems porting to my hoster's server.

To transfer the database contents, I've exported the structure and data of
my local DB to a .sql file and uploaded this to my hosted DB using
phpMyAdmin on the host. This seems to work, but there are two problems:

1). Firstly, the MySQL PASSWORD() command give different results on the host
than it does on my local machine. For example, using phpMyAdmin on my local
machine, typing:

SELECT PASSWORD ('mypassword') Your hoster has a newer MySQL version than you have. If you want to use
your scripts, replace PASSWORD through OLD_PASSWORD.

See http://dev.mysql.com/doc/refman/5.0/...d-hashing.html for
more information.
gives a 16 byte number as my books suggest. However, running the same
command on the server gives a 41 bytes code!!!. Consequently, all my login
functionality does not work!.
Leaving 2 for the others due to lack of knowledge ;)
Thanks for any comments,
Dave

hth,
rauch

[Dave Moore]

Thanks Rauch. That explains a lot.

"Rauch Christian" <in**@rauch-webdesign.de> wrote in message
news:di******** *****@news.t-online.com...

Dave Moore schrieb:

Hi All,
I'm putting a website together using PHP and a MySQL database. I've been using phpMyAdmin as it makes updating the DB nice and easy. For development, I've been using Apache, MySQL and PHP installed on my local machine but now I'm having problems porting to my hoster's server.

To transfer the database contents, I've exported the structure and data of my local DB to a .sql file and uploaded this to my hosted DB using
phpMyAdmin on the host. This seems to work, but there are two problems:

1). Firstly, the MySQL PASSWORD() command give different results on the host than it does on my local machine. For example, using phpMyAdmin on my local machine, typing:

SELECT PASSWORD ('mypassword')

Your hoster has a newer MySQL version than you have. If you want to use
your scripts, replace PASSWORD through OLD_PASSWORD.

See http://dev.mysql.com/doc/refman/5.0/...d-hashing.html for
more information.

gives a 16 byte number as my books suggest. However, running the same
command on the server gives a 41 bytes code!!!. Consequently, all my login functionality does not work!.

Leaving 2 for the others due to lack of knowledge ;)

Thanks for any comments,
Dave

hth,
rauch

[Axel Schwenke]

"Dave Moore" <da**********@p ost2me.freeserv e.co.uk> wrote:

For development,
I've been using Apache, MySQL and PHP installed on my local machine but now
I'm having problems porting to my hoster's server. .... but there are two problems:

1). Firstly, the MySQL PASSWORD() command give different results on the host
than it does on my local machine. SELECT PASSWORD ('mypassword')

gives a 16 byte number as my books suggest. However, running the same
command on the server gives a 41 bytes code!!!. Consequently, all my login
functionality does not work!. ....
2). Secondly, I noticed that phpMyAdmin on the host shows a 'Collation'
column with the value 'latin1_swedish _ci'. This column isn't present when
using phpMyAdmin on my local machine, even though the DB contents show be
identical. So what's that all about??.

As Christian said, you hoster uses a newer MySQL version than you. Both
features were introduced with MySQL 4.1. I suggest you upgrade your
development machine to the latest 4.1 version of MySQL.

Regarding PASSWORD(): the MySQL manual [1] strongly discourages from
using PASSWORD() for your own applications and recommends MD5() or
SHA1() for password hashing.

Regarding character sets and collations: this is great stuff if one
wants to build a multilingual website. For a unilingual application
it's enough to set the defaults for the used tables/databases to the
right values. I suggest reading the manual [2].
[1] http://dev.mysql.com/doc/refman/5.0/...functions.html
[2] http://dev.mysql.com/doc/refman/5.0/en/charset.html
XL

[Christopher Pomasl]

On Thu, 13 Oct 2005 10:35:42 +0200, Axel Schwenke wrote:

"Dave Moore" <da**********@p ost2me.freeserv e.co.uk> wrote:

For development,
I've been using Apache, MySQL and PHP installed on my local machine but now
I'm having problems porting to my hoster's server.

...

but there are two problems:

1). Firstly, the MySQL PASSWORD() command give different results on the host
than it does on my local machine.

SELECT PASSWORD ('mypassword')

gives a 16 byte number as my books suggest. However, running the same
command on the server gives a 41 bytes code!!!. Consequently, all my login
functionality does not work!.

...

2). Secondly, I noticed that phpMyAdmin on the host shows a 'Collation'
column with the value 'latin1_swedish _ci'. This column isn't present when
using phpMyAdmin on my local machine, even though the DB contents show be
identical. So what's that all about??.

As Christian said, you hoster uses a newer MySQL version than you. Both
features were introduced with MySQL 4.1. I suggest you upgrade your
development machine to the latest 4.1 version of MySQL.

Regarding PASSWORD(): the MySQL manual [1] strongly discourages from
using PASSWORD() for your own applications and recommends MD5() or
SHA1() for password hashing.

Regarding character sets and collations: this is great stuff if one
wants to build a multilingual website. For a unilingual application
it's enough to set the defaults for the used tables/databases to the
right values. I suggest reading the manual [2].
[1] http://dev.mysql.com/doc/refman/5.0/...functions.html
[2] http://dev.mysql.com/doc/refman/5.0/en/charset.html
XL

Correct me if I'm wrong.
Using the MD5() hash hashes the password into an unrecoverable string. So
when you then ask for a password from your user, you need to MD5() the
given password and compare that to the stored hash.

This is what I'm doing but it also causes you to not be able to
recover/discover a password for a user. You can then only reset the
password if the user forgets theirs.

I combine the password with a number of pertinent facts about the user,
name etc, plus a string of my own choosing. I then combine all this stuff
and hash with MD5(), the PHP function not with MySQL, and store the
resulting string in the DB. Then at log in, I pull the DB info for the
user attempting to log in, by userid, and hash this info with the given
password and compare it to the stored password.

Works so far.....

Chris