Encryption with MYSQL

--00c09f9233bfc773f3049072c88f
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I need to encrypt a string like 'hello world', using a passkey. But I also
need to be able to decrypt the encrypted phrase using the same passkey. I
noticed in MySQL there are functions like
AES_ENCRYPT()

However, I need the encrypted phrase to be returned as a string, since it
will be passed to a URL as a parameter. Does anyone have any suggestions on
how to overcome this issue, using MySQL.

I know that there are many components available out there, but I can't
install any third party components on the hosting server.

Cheers
Neil

--00c09f9233bfc773f3049072c88f--

Re: Encryption with MYSQL

--000e0cd52e70e5473a0490737433
Content-Type: text/plain; charset=ISO-8859-1

Simply base64-encode the returned binary string before offering it to your
client.

On Fri, Sep 17, 2010 at 1:22 PM, Tompkins Neil > wrote:

> Hi,
>
> I need to encrypt a string like 'hello world', using a passkey. But I also
> need to be able to decrypt the encrypted phrase using the same passkey. I
> noticed in MySQL there are functions like
> AES_ENCRYPT()<
> http://dev.mysql.com/doc/refman/5.1/en/encryption-functions. html#function_aes-encrypt
> >
>
> However, I need the encrypted phrase to be returned as a string, since it
> will be passed to a URL as a parameter. Does anyone have any suggestions
> on
> how to overcome this issue, using MySQL.
>
> I know that there are many components available out there, but I can't
> install any third party components on the hosting server.
>
> Cheers
> Neil
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--000e0cd52e70e5473a0490737433--

prime number table

I got a file of the list of the 1st 1270607 prime numbers (the 1270607th
prime is 19999999,
beat the $227 book at
http://www.amazon.com/prime-numbers-Carnegie-institution-Was hington/dp/B0006AH1S8).
the file is an output of a python script. the file size is about 12Mb.

Then I created a simeple mysql table prime as

mysql> desc prime;
+----------+------------------+------+-----+---------+------ ----------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+------ ----------+
| oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pv | int(10) unsigned | YES | | NULL | |
| descript | text | YES | | NULL | |
+----------+------------------+------+-----+---------+------ ----------+
mysql> show create table prime;
------------------------------------------------------------ --------------+
| Table | Create Table
------------------------------------------------------------ --------------+
| prime | CREATE TABLE `prime` (
`oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pv` int(10) unsigned DEFAULT NULL,
`descript` text,
PRIMARY KEY (`oid`)
) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1

The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k; 24,817KB;
12,754KB)

Then I do
mysql> create table prm select * from prime order by prime.oid;
mysql> alter table prm modify oid int unsigned primary key auto_increment;

mysql> desc prm;
+----------+------------------+------+-----+---------+------ ----------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+------ ----------+
| oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pv | int(10) unsigned | YES | | NULL | |
| descript | text | YES | | NULL | |
+----------+------------------+------+-----+---------+------ ----------+

mysql> show create table prm;
+-------+--------------------------------------------------- ---------------
| Table | Create Table
+-------+--------------------------------------------------- ---------------
| prm | CREATE TABLE `prm` (
`oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pv` int(10) unsigned DEFAULT NULL,
`descript` text,
PRIMARY KEY (`oid`)
) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------- ---------------

The table file prm.frm is only 9KB

My question is that how come it's SO SMALL? (currently the colum description
in both tables prime and prm are empty except one identical row, with very
short string value.

Is is recommend to index the other twoo columns?

Thanks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: prime number table

The data isn't in the .frm; That only holds the structure of the table.

Your data is in the .MYD file of the same name, and indexes are in the .MYI=
file.

-----Original Message-----
From: Elim PDT [mailto:elim@pdtnetworks.net]
Sent: Friday, September 17, 2010 11:29 AM
To: mysql@lists.mysql.com
Subject: prime number table

I got a file of the list of the 1st 1270607 prime numbers (the 1270607th
prime is 19999999,
beat the $227 book at
http://www.amazon.com/prime-numbers-Carnegie-institution-Was hington/dp/B000=
6AH1S8).
the file is an output of a python script. the file size is about 12Mb.

Then I created a simeple mysql table prime as

mysql> desc prime;
+----------+------------------+------+-----+---------+------ ----------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+------ ----------+
| oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pv | int(10) unsigned | YES | | NULL | |
| descript | text | YES | | NULL | |
+----------+------------------+------+-----+---------+------ ----------+
mysql> show create table prime;
------------------------------------------------------------ --------------+
| Table | Create Table
------------------------------------------------------------ --------------+
| prime | CREATE TABLE `prime` (
`oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pv` int(10) unsigned DEFAULT NULL,
`descript` text,
PRIMARY KEY (`oid`)
) ENGINE=3DMyISAM AUTO_INCREMENT=3D1270608 DEFAULT CHARSET=3Dlatin1

The table file size is (prime.frm,prime.MYD,prime.MYI) =3D (9k; 24,817KB;
12,754KB)

Then I do
mysql> create table prm select * from prime order by prime.oid;
mysql> alter table prm modify oid int unsigned primary key auto_increment;

mysql> desc prm;
+----------+------------------+------+-----+---------+------ ----------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+------ ----------+
| oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| pv | int(10) unsigned | YES | | NULL | |
| descript | text | YES | | NULL | |
+----------+------------------+------+-----+---------+------ ----------+

mysql> show create table prm;
+-------+--------------------------------------------------- ---------------
| Table | Create Table
+-------+--------------------------------------------------- ---------------
| prm | CREATE TABLE `prm` (
`oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pv` int(10) unsigned DEFAULT NULL,
`descript` text,
PRIMARY KEY (`oid`)
) ENGINE=3DInnoDB AUTO_INCREMENT=3D1270608 DEFAULT CHARSET=3Dlatin1 |
+-------+--------------------------------------------------- ---------------

The table file prm.frm is only 9KB

My question is that how come it's SO SMALL? (currently the colum descriptio=
n
in both tables prime and prm are empty except one identical row, with very
short string value.

Is is recommend to index the other twoo columns?

Thanks


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: prime number table

Keep in mind that to store a number as a 32 bit unsigned integer takes
4 bytes of data no matter how small or large that number is as long as
it is less than 4,294,267,296. If you store numbers as a string like it
is in your file it takes 8 bits per digit so for 19,999,999 it would
take 8 bytes or 64 bits. I assume the file has a line feed between each
number, that is another 8 bits and may also have a carriage return
adding another 8 bits.

You could probably make your table even smaller if you were to move the
descript filed to a new table. I highly recommend this option based on
your statement that most of them are empty. A table with only integer
values will have a fixed row size and probably be a little faster to
access. If most of the rows will have descript text them leave it in
this table, other wise it would be more efficient to keep in a different
table.

Also I'm not sure you really need an independent field for a primary key
since the prime numbers could be used as the primary key. That would
make your table even smaller. I'm not sure why you would what a table
of prime numbers unless you had an index on that field and making it the
primary key does that for you.

Chris W

On 9/17/2010 1:28 PM, Elim PDT wrote:
> I got a file of the list of the 1st 1270607 prime numbers (the
> 1270607th prime is 19999999,
> beat the $227 book at
> http://www.amazon.com/prime-numbers-Carnegie-institution-Was hington/dp/B0006AH1S8).
> the file is an output of a python script. the file size is about 12Mb.
>
> Then I created a simeple mysql table prime as
>
> mysql> desc prime;
> +----------+------------------+------+-----+---------+------ ----------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------------+------+-----+---------+------ ----------+
> | oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
> | pv | int(10) unsigned | YES | | NULL | |
> | descript | text | YES | | NULL | |
> +----------+------------------+------+-----+---------+------ ----------+
> mysql> show create table prime;
> ------------------------------------------------------------ --------------+
>
> | Table | Create Table
> ------------------------------------------------------------ --------------+
>
> | prime | CREATE TABLE `prime` (
> `oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `pv` int(10) unsigned DEFAULT NULL,
> `descript` text,
> PRIMARY KEY (`oid`)
> ) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1
>
> The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k;
> 24,817KB; 12,754KB)
>
> Then I do
> mysql> create table prm select * from prime order by prime.oid;
> mysql> alter table prm modify oid int unsigned primary key
> auto_increment;
>
> mysql> desc prm;
> +----------+------------------+------+-----+---------+------ ----------+
> | Field | Type | Null | Key | Default | Extra |
> +----------+------------------+------+-----+---------+------ ----------+
> | oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
> | pv | int(10) unsigned | YES | | NULL | |
> | descript | text | YES | | NULL | |
> +----------+------------------+------+-----+---------+------ ----------+
>
> mysql> show create table prm;
> +-------+--------------------------------------------------- ---------------
>
> | Table | Create Table
> +-------+--------------------------------------------------- ---------------
>
> | prm | CREATE TABLE `prm` (
> `oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
> `pv` int(10) unsigned DEFAULT NULL,
> `descript` text,
> PRIMARY KEY (`oid`)
> ) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 |
> +-------+--------------------------------------------------- ---------------
>
>
> The table file prm.frm is only 9KB
>
> My question is that how come it's SO SMALL? (currently the colum
> description
> in both tables prime and prm are empty except one identical row, with
> very
> short string value.
>
> Is is recommend to index the other twoo columns?
>
> Thanks
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Encryption with MYSQL

--0016364ed0a40edb940490ad2108
Content-Type: text/plain; charset=ISO-8859-1

Any ideas why my statement like SELECT SHA1('abc') AS my_sha is being
returned as binary value, I thought it should be returning HEX in anycase ?
I'm using MySQL community server 5.1.43, with Innodb tables.

Cheers
Neil

On Sat, Sep 18, 2010 at 10:43 AM, Johan De Meersman wrote:

> hex() and unhex() should do the trick in mysql (not base64, but same idea).
> Those will transform every byte into a pair of hexadecimal digits - thus
> limited to 0-9 and A-F - and reverse the process.
>
>
> On Fri, Sep 17, 2010 at 2:13 PM, Tompkins Neil <
> neil.tompkins@googlemail.com> wrote:
>
>> Sorry Johan, how exactly do you do this ?
>>
>>
>> On Fri, Sep 17, 2010 at 1:11 PM, Johan De Meersman wrote:
>>
>>> Simply base64-encode the returned binary string before offering it to
>>> your client.
>>>
>>> On Fri, Sep 17, 2010 at 1:22 PM, Tompkins Neil <
>>> neil.tompkins@googlemail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> I need to encrypt a string like 'hello world', using a passkey. But I
>>>> also
>>>> need to be able to decrypt the encrypted phrase using the same passkey.
>>>> I
>>>> noticed in MySQL there are functions like
>>>> AES_ENCRYPT()<
>>>> http://dev.mysql.com/doc/refman/5.1/en/encryption-functions. html#function_aes-encrypt
>>>> >
>>>>
>>>>
>>>> However, I need the encrypted phrase to be returned as a string, since
>>>> it
>>>> will be passed to a URL as a parameter. Does anyone have any
>>>> suggestions on
>>>> how to overcome this issue, using MySQL.
>>>>
>>>> I know that there are many components available out there, but I can't
>>>> install any third party components on the hosting server.
>>>>
>>>> Cheers
>>>> Neil
>>>>
>>>
>>>
>>>
>>> --
>>> Bier met grenadyn
>>> Is als mosterd by den wyn
>>> Sy die't drinkt, is eene kwezel
>>> Hy die't drinkt, is ras een ezel
>>>
>>
>>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>

--0016364ed0a40edb940490ad2108--

Re: Encryption with MYSQL

Hi,

On Mon, Sep 20, 2010 at 4:59 AM, Tompkins Neil
wrote:
> Any ideas why my statement like SELECT SHA1('abc') AS my_sha is being
> returned as binary value, I thought it should be returning HEX in anycase ?

Maybe because that's what it's intended to do.
http://dev.mysql.com/doc/refman/5.1/en/encryption-functions. html#function_sha1

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: prime number table

Thank you so much Chris.
But is this mean that myisam table stores integer very differntly and
inefficiently than InnoDB table?

I see that oid can be ommitted. But in the case that I like to query
on ordinal number of the prime. oid can make things faster, am i right?

seperate the storage for descript is a great idea. thanks again!



----- Original Message -----
From: "Chris W" <4rfvgy7@cox.net>
To: "Elim PDT"
Cc:
Sent: Saturday, September 18, 2010 6:57 AM
Subject: Re: prime number table


> Keep in mind that to store a number as a 32 bit unsigned integer takes
> 4 bytes of data no matter how small or large that number is as long as
> it is less than 4,294,267,296. If you store numbers as a string like it
> is in your file it takes 8 bits per digit so for 19,999,999 it would
> take 8 bytes or 64 bits. I assume the file has a line feed between each
> number, that is another 8 bits and may also have a carriage return
> adding another 8 bits.
>
> You could probably make your table even smaller if you were to move the
> descript filed to a new table. I highly recommend this option based on
> your statement that most of them are empty. A table with only integer
> values will have a fixed row size and probably be a little faster to
> access. If most of the rows will have descript text them leave it in
> this table, other wise it would be more efficient to keep in a different
> table.
>
> Also I'm not sure you really need an independent field for a primary key
> since the prime numbers could be used as the primary key. That would
> make your table even smaller. I'm not sure why you would what a table
> of prime numbers unless you had an index on that field and making it the
> primary key does that for you.
>
> Chris W
>
> On 9/17/2010 1:28 PM, Elim PDT wrote:
>> I got a file of the list of the 1st 1270607 prime numbers (the
>> 1270607th prime is 19999999,
>> beat the $227 book at
>> http://www.amazon.com/prime-numbers-Carnegie-institution-Was hington/dp/B0006AH1S8).
>> the file is an output of a python script. the file size is about 12Mb.
>>
>> Then I created a simeple mysql table prime as
>>
>> mysql> desc prime;
>> +----------+------------------+------+-----+---------+------ ----------+
>> | Field | Type | Null | Key | Default | Extra |
>> +----------+------------------+------+-----+---------+------ ----------+
>> | oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
>> | pv | int(10) unsigned | YES | | NULL | |
>> | descript | text | YES | | NULL | |
>> +----------+------------------+------+-----+---------+------ ----------+
>> mysql> show create table prime;
>> ------------------------------------------------------------ --------------+
>>
>> | Table | Create Table
>> ------------------------------------------------------------ --------------+
>>
>> | prime | CREATE TABLE `prime` (
>> `oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
>> `pv` int(10) unsigned DEFAULT NULL,
>> `descript` text,
>> PRIMARY KEY (`oid`)
>> ) ENGINE=MyISAM AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1
>>
>> The table file size is (prime.frm,prime.MYD,prime.MYI) = (9k;
>> 24,817KB; 12,754KB)
>>
>> Then I do
>> mysql> create table prm select * from prime order by prime.oid;
>> mysql> alter table prm modify oid int unsigned primary key
>> auto_increment;
>>
>> mysql> desc prm;
>> +----------+------------------+------+-----+---------+------ ----------+
>> | Field | Type | Null | Key | Default | Extra |
>> +----------+------------------+------+-----+---------+------ ----------+
>> | oid | int(10) unsigned | NO | PRI | NULL | auto_increment |
>> | pv | int(10) unsigned | YES | | NULL | |
>> | descript | text | YES | | NULL | |
>> +----------+------------------+------+-----+---------+------ ----------+
>>
>> mysql> show create table prm;
>> +-------+--------------------------------------------------- ---------------
>>
>> | Table | Create Table
>> +-------+--------------------------------------------------- ---------------
>>
>> | prm | CREATE TABLE `prm` (
>> `oid` int(10) unsigned NOT NULL AUTO_INCREMENT,
>> `pv` int(10) unsigned DEFAULT NULL,
>> `descript` text,
>> PRIMARY KEY (`oid`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=1270608 DEFAULT CHARSET=latin1 |
>> +-------+--------------------------------------------------- ---------------
>>
>>
>> The table file prm.frm is only 9KB
>>
>> My question is that how come it's SO SMALL? (currently the colum
>> description
>> in both tables prime and prm are empty except one identical row, with
>> very
>> short string value.
>>
>> Is is recommend to index the other twoo columns?
>>
>> Thanks
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=elim@pdtnetworks.net
>


------------------------------------------------------------ --------------------



No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.851 / Virus Database: 271.1.1/3143 - Release Date: 09/18/10
00:34:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org