odd characters in SQL queries doing odd things to putty

[LRW]

I'm sorry, I don't know if this is a mySQL issue, or a Putty error, or
what. So if there's a better newsgroup for this question, please let
me know.

I'm using Putty to SSH into our remote Linux server. On one of our
mySQL databases, we have a table of users. One of the fields used to
store passwords we have as type "BLOG" and we use the ENCODE/DECODE to
encrypt passwords.

Now, when we do a select of a row from that table, because the
password field is encrypted and displays funky ASCII characters
instead of a password, I think some of those characters are being
interpreted by Putty as formatting characters because it will do some
weird things with the display.

Like this:
mysql> select * from tbl_users;
+----+----------+----------+-------------+----------+--------+----------------------------------------+-------+
| id | username | password | fullname | created | active | email
| admin |
+----+----------+----------+-------------+----------+--------+----------------------------------------+-------+
| 1 | liam | ËæËØ | LRW | 20040406 | 1 |
li**@SNIP.com | 1 |
| 2 | chipper | ÛÐ-]¿í=j | Super Duane | 20040407 | 0 |
| 0 |
| 3 | mccarter | ýàõJ¾]" | Angela | 20040412 | 1 |
cu*************@SNIP.com | 0 |
×e 4 | sarah | 0
LÒ | Sarah | 20040412 | 1 | sa***@SNIP.com | 0
|
| 5 | duane | ªéüXòTÜ | Duane | 20040412 | 1 |
de******@SNIP.com | 0 |
| 6 | Jerry1 | áÖJì | Jerry | 20040422 | 1 |
je***@SNIP.com | 0 |
| 7 | bryan | ÛÐ-]ÍJ | Bryan K | 20040512 | 1 |
| 1 |
| 8 | david | ÛÐ-éÍJ | David V | 20040512 | 1 |
| 0 |
}¼=j | Ken K | 20040512 | 1 |
| 1 |
+----+----------+----------+-------------+----------+--------+----------------------------------------+-------+
9 rows in set (0.00 sec)

I looked through Putty settings, and can't find anything that seems to
have anything to do with this issue.
So, any suggestions?

Thanks!
Liam

[Bill Karwin]

LRW wrote:

I'm using Putty to SSH into our remote Linux server. On one of our
mySQL databases, we have a table of users. One of the fields used to
store passwords we have as type "BLOG"
I think you mean "BLOB".
and we use the ENCODE/DECODE to encrypt passwords.
MySQL's ENCODE function produces a sequence of binary bytes, which means
that it contains non-ASCII data. These are not necessarily viewable in
any terminal session, because they don't map to text characters. That's
the point of encrypting data, to make the original values unreadable for
those who don't know the decryption password.
Now, when we do a select of a row from that table, because the
password field is encrypted and displays funky ASCII characters
instead of a password

.. . .

If you want to see the original passwords, you must decrypt them:

SELECT DECODE(password, 'decode-password') FROM tbl_users;

where 'decode-password' is the string you used when encoding the
passwords when you entered them. If you have forgotten that decode
password, you have lost the ability to view the original password strings.

Regards,
Bill K.

[Juhan Leemet]

On Mon, 26 Jul 2004 14:27:23 -0700, Bill Karwin wrote:

LRW wrote:

I'm using Putty to SSH into our remote Linux server...
Not relevant.
and we use the ENCODE/DECODE to encrypt passwords.

MySQL's ENCODE function produces a sequence of binary bytes, which means
that it contains non-ASCII data. These are not necessarily viewable...

Now, when we do a select of a row from that table, because the password
field is encrypted and displays funky ASCII characters...

If you want to see the original passwords, you must decrypt them:

The corollary is that if you don't want the "funky non-ASCII" then you
should not select that field as part of your selected row. I think the
only way (you cannot deselect a row) is to specify all the other fields
that you want to select. I know: PITA.

How about putting all your encrypted passwords into a separate
"encrypted_passwords" table? Use the UID as a link field between tables.
Then you would NEVER do a select from the encrypted password table,
unless you are actually decrypting it for use. Your normal select from
your user table would only show the UID (used to link to the password
table) which is printable (and not particularly secret). This is quite
common setup for database tables.

--
Juhan Leemet
Logicognosis, Inc.

[Bill Karwin]

Juhan Leemet wrote:

The corollary is that if you don't want the "funky non-ASCII" then you
should not select that field as part of your selected row. I think the
only way (you cannot deselect a row) is to specify all the other fields
that you want to select. I know: PITA.

You could select all columns, as well as a computed column:
select t.*, decode(t.password, 'key')
from mytable t
and then simply ignore the column that contains the encrypted binary data.

If I may make a brief tangent from the original question, I believe it
is a good practice when programming in SQL to avoid using "select *" and
also avoid using "insert into mytable values (...)" (that is, without
specifying the columns to which you are inserting). In both cases, your
SQL code might break or give unforeseen results if you add columns to
your tables.

Regards,
Bill K.

[LRW]

Bill Karwin <bi**@karwin.com> wrote in message news:<ce*********@enews1.newsguy.com>...

You could select all columns, as well as a computed column:
select t.*, decode(t.password, 'key')
from mytable t
and then simply ignore the column that contains the encrypted binary data.

If I may make a brief tangent from the original question, I believe it
is a good practice when programming in SQL to avoid using "select *" and
also avoid using "insert into mytable values (...)" (that is, without
specifying the columns to which you are inserting). In both cases, your
SQL code might break or give unforeseen results if you add columns to
your tables.

Thanks Bill and Juhan for your replies. I would NEVER do an INSERT
without defining the fields! But doing a SELECT * is just so easy. =)
Especially when in a querey you need to see more fields than not.
I LOVE the idea of a separate table for the passwords. I mean, that
makes perfect, obvious sense!
Thanks,
Liam