Ben ended with this recommendation
begin
set NEW.record_uid_hash = MD5(UUID());
end
however, it would be better to remove the MD5, to guarantee no
collision.
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park Dr
Magalia, Ca 95954
ph: 530.645.4040 x222 fax: 530.645.4040
[EMAIL PROTECTED] - [EMAIL PROTECTED]
http://bighead.net/ - http://eventpix.com/
On Jun 2, 2008, at 10:14 PM, William Conlon wrote:
Yes. A HASH is NOT one-to-one and ONTO; it's merely one-way.
The likelihood of a Hash collision may be small, but it's not zero.
On Jun 2, 2008, at 10:09 PM, Ben Johansen wrote:
Wait a second!
are you saying that if i have an auto increment uid field that will
have a constant changing value, that md5 is going to produce
duplicates?
Ben
On Jun 2, 2008, at 10:04 PM, William Conlon wrote:
Is there a point to this exercise?
the autoincrement record_uid == last_insert_id is unique.
MD5(last_insert_id()) is:
1) not unique -- there can be a hash collision
2) subject to replay attack
If the point is to convert the record_uid so you can expose it in
a web app, why not use ENCODE/DECODE on (record_uid)?
--bill
On Jun 2, 2008, at 9:43 PM, Robert Garcia wrote:
Just thinking....
I know that last_insert_id() is SESSION specific. So it only
applies to the session of a connection. Could there be another
function, that gets the last insert id, with a global scope? I
know MSSQL has this, in session, and global scope. I also saw
this somewhere:
If you want store the last id for use in multiple insert
statements:
create table person (
id bigint not null primary key auto_increment,
name char(100) not null
);
insert into person set name = 'Joe';
select @id:=id as id from class where id = last_insert_id();
insert into some_other_table set person_id = @class_id;
insert into yet_another_table set person_id = @class_id;
--
Robert Garcia
President - BigHead Technology
VP Application Development - eventpix.com
13653 West Park Dr
Magalia, Ca 95954
ph: 530.645.4040 x222 fax: 530.645.4040
[EMAIL PROTECTED] - [EMAIL PROTECTED]
http://bighead.net/ - http://eventpix.com/
On Jun 2, 2008, at 7:49 PM, Ben Johansen wrote:
this one was interesting,
at first look you would think you could do
-on before insert trigger
begin
set NEW.record_uid_hash = MD5(NEW.record_uid);
end
and in fact I was able to get that trigger to save, but each run
yielded the same hash value.
so apparently it was hashing an empty value, because the auto-
increment values is not in the NEW yet
also under navicat I found that if you get an error (trigger not
defined) when defining a trigger all the info will remain on the
screen, but the trigger will be gone for that edit session you
have to copy the trigger content into the buffer and then close
and then restart the trigger define process.
That said, I found that I tried LAST_INSERT_ID()
-on before insert trigger
begin
declare vHashValue varchar(60);
set vHashValue = md5(convert(LAST_INSERT_ID(),CHAR));
set NEW.record_uid_hash = vHashValue;
end
but the LAST_INSERT_ID() i just that the last successful insert
into table with autoincrement
so this proved fruitless, and in looking thru the docs there is
apparently now way in a function to get the next auto increment
value.
so in looking at what is needed and that is just unique md5 hash
to be used as a cross-reference to the row id and needing to be
unique
I came up with this.
-on before insert trigger
begin
set NEW.record_uid_hash = MD5(UUID());
end
doing the md5 on uuid takes the big uuid down to a smaller 32
char md5
Ben
On Jun 2, 2008, at 4:18 PM, Dan Stein wrote:
I am new to using MYSQL usually use MSSQL.
I have a table called record
pk is record_uid (auto increments)
there is a column called record_uid_hash
this is a MD5 of record_uid
I want to create a trigger so when a new record is created the
MD5 of the record_uid is entered into the row.
MD5(record_uid)
I can't seem to get the syntax correct.
I use NAVACAT to admin the DB I am getting a message saying no
such trigger.
Any help would be appreciated.
Dan
--
Dan Stein
FileMaker 7 Certified Developer
Digital Software Solutions
799 Evergreen Circle
Telford PA 18969
Land: 215-799-0192
Cell: 610-256-2843
FMP, WiTango, EDI,SQL 2000, MySQL, PHP
[EMAIL PROTECTED]
www.dss-db.com
"The love of one's country is a splendid thing. But why should
love stop at the border? "
Pablo Casals
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/
maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________
TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
