SEO

Hello all -

I have a problem with a table, and I think it has to do with UNIQUE
key length.

I have a table with a client_id column, and a guid column. The guid
column is a 32-character MD5 hash of a text value.

I do a
SELECT *
FROM table
WHERE client_id = 1
AND guid = 'ba083c1576f8c555ef4ff0ae09dd33a7'

and I get now results. Then, I do

INSERT INTO table (
client_id,
guid
) VALUES (
1,
'ba083c1576f8c555ef4ff0ae09dd33a7'
);

And I get "Duplicate entry 'ba083c1576f8c555ef4ff0ae09dd33a7-1' for
key 2", where key 2 is UNIQUE(client_id, guid) . Which is confusing;
because I don't have a record
ba083c1576f8c555ef4ff0ae09dd33a7, 1
but I do have
ba083c1576f8c555ef4ff0ae09dd33a7, 2

If the key is shorter than 32 characters, then the guid with client_id
=2 would be the same as client_id = 1.

Am I getting the error because my UNIQUE key is too long? This is
MySQL 5.0.24a.

Is there a way to actually look at the values for the keys in my table?

On 21 Apr, 19:14, lawp...@gmail.com wrote:
> Hello all -
>
> I have a problem with a table, and I think it has to do with UNIQUE
> key length.
>
> I have a table with a client_id column, and a guid column. The guid
> column is a 32-character MD5 hash of a text value.
>
> I do a
> SELECT *
> FROM table
> WHERE client_id = 1
> AND guid = 'ba083c1576f8c555ef4ff0ae09dd33a7'
>
> and I get now results. Then, I do
>
> INSERT INTO table (
> client_id,
> guid
> ) VALUES (
> 1,
> 'ba083c1576f8c555ef4ff0ae09dd33a7'
> );
>
> And I get "Duplicate entry 'ba083c1576f8c555ef4ff0ae09dd33a7-1' for
> key 2", where key 2 is UNIQUE(client_id, guid) . Which is confusing;
> because I don't have a record
> ba083c1576f8c555ef4ff0ae09dd33a7, 1
> but I do have
> ba083c1576f8c555ef4ff0ae09dd33a7, 2
>
> If the key is shorter than 32 characters, then the guid with client_id
> =2 would be the same as client_id = 1.
>
> Am I getting the error because my UNIQUE key is too long? This is
> MySQL 5.0.24a.
>
> Is there a way to actually look at the values for the keys in my table?

Can you export the table structure? Why are there 2 keys?

On Apr 21, 2:27 pm, "petethebl...@googlemail.com"
<petethebl...@googlemail.com> wrote:

> Can you export the table structure? Why are there 2 keys?

Well, the table structure is sort of confidential, I really can't
export it.

As far as two keys, I'm maybe using the terminology wrong. I have a
UNIQUE key on two columns. Each client should only have one instance
of the text in the table, so the key is UNIQUE(text_hash, client_id).

On 21 Apr, 19:48, lawp...@gmail.com wrote:
> On Apr 21, 2:27 pm, "petethebl...@googlemail.com"
>
> <petethebl...@googlemail.com> wrote:
> > Can you export the table structure? Why are there 2 keys?
>
> Well, the table structure is sort of confidential, I really can't
> export it.
>
> As far as two keys, I'm maybe using the terminology wrong. I have a
> UNIQUE key on two columns. Each client should only have one instance
> of the text in the table, so the key is UNIQUE(text_hash, client_id).

I'm no expert on terminology either

I can't see why you get the results you describe here...
> SELECT *
> FROM table
> WHERE client_id = 1
> AND guid = 'ba083c1576f8c555ef4ff0ae09dd33a7'
>
> and I get now results. Then, I do

> INSERT INTO table (
> client_id,
> guid
> ) VALUES (
> 1,
> 'ba083c1576f8c555ef4ff0ae09dd33a7'
> );

But if you could show us the datatypes and give us a bit more detail
about the keys, index and wotnot? I'm sure you'll be resourceful
enough to do that without giving away any confidential info

Pete

On Mon, 21 Apr 2008 20:14:21 +0200, <lawpoop@gmail.com> wrote:
> I have a problem with a table, and I think it has to do with UNIQUE
> key length.
>
> I have a table with a client_id column, and a guid column. The guid
> column is a 32-character MD5 hash of a text value.
>
> I do a
> SELECT *
> FROM table
> WHERE client_id = 1
> AND guid = 'ba083c1576f8c555ef4ff0ae09dd33a7'
>
> and I get now results. Then, I do
>
> INSERT INTO table (
> client_id,
> guid
> ) VALUES (
> 1,
> 'ba083c1576f8c555ef4ff0ae09dd33a7'
> );
>
> And I get "Duplicate entry 'ba083c1576f8c555ef4ff0ae09dd33a7-1' for
> key 2", where key 2 is UNIQUE(client_id, guid) . Which is confusing;
> because I don't have a record
> ba083c1576f8c555ef4ff0ae09dd33a7, 1
> but I do have
> ba083c1576f8c555ef4ff0ae09dd33a7, 2
>
> If the key is shorter than 32 characters, then the guid with client_id
> =2 would be the same as client_id = 1.
>
> Am I getting the error because my UNIQUE key is too long? This is
> MySQL 5.0.24a.
>
> Is there a way to actually look at the values for the keys in my table?


mysql> CREATE TABLE `unique_test` (
-> `foo` varchar(32) default NULL,
-> `bar` int(4) default NULL,
-> UNIQUE KEY (`foo`,`bar`)
-> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into unique_test value
('12345678912345678912345678912345',1);
Query OK, 1 row affected (0.14 sec)

mysql> insert into unique_test value
('12345678912345678912345678912345',2);
Query OK, 1 row affected (0.01 sec)

No problems here whatsoever. Your UNIQUE is not properly created. The
fastest way to show the key is by issuing a SHOW CREATE TABLE <tablename>;
query.
--
Rik Wasmus

lawpoop@gmail.com wrote:
> On Apr 21, 2:27 pm, "petethebl...@googlemail.com"
> <petethebl...@googlemail.com> wrote:
>
>> Can you export the table structure? Why are there 2 keys?
>
> Well, the table structure is sort of confidential, I really can't
> export it.
Confidential!!! It will be a list of fields with data types, sizes and field
names. That is what all tables look like. How the !"!"££ can it be
confidential!

On 21 Apr, 22:40, "Paul Lautman" <paul.laut...@btinternet.com> wrote:

> Confidential!!! It will be a list of fields with data types, sizes and field
> names. That is what all tables look like. How the !"!"££ can it be
> confidential!

That's what I was trying to say, but more politely.

On Apr 22, 5:46 am, "petethebl...@googlemail.com"
<petethebl...@googlemail.com> wrote:
> On 21 Apr, 22:40, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>
> > Confidential!!! It will be a list of fields with data types, sizes and field
> > names. That is what all tables look like. How the !"!"££ can it be
> > confidential!
>
> That's what I was trying to say, but more politely.

Well, if we have a database that's available to the public through a
website, and some entreprising hacker wants to do an SQL injection,
they would get quite a leg up if they could look up the exact names of
our fields and tables.

Of course we are taking every precaution to prevent SQL injections and
other hacking attemps, and not broadcasting table definitions on the
internet is part of that

As far as confidential, saying that no table definition can be
confidential because it's made up of the same parts ( tables and
fields ) as any other database is like saying all programs are the
same because they're all made up of vairable and operators. It's the
arrangement of the parts that adds value. Surely, you would agree that
certain database structures are better than others for particular
problems. Or all they all the same? If they aren't the same, then a
business person who invested money to get a better data model might
want to protect their investment.

Why couldn't Microsoft release the code for Windows? After all, it's
made up of the same parts as any other program. And I'm not talking
security wise, but competition-wise -- for example, not giving OS
providers a leg up in creating platforms to run software written to
run with the Windows API.

Suppose we have a business model, expressed as a database, that is
superior to our competitions', and that allows us to ship products
faster for less money. Why would we want to then give our model to our
competition? We spent a lot of time and money building it; it doesn't
make business sense to give it to your competition for free.

</OT rant>

On Tue, 22 Apr 2008 21:29:28 +0200, <lawpoop@gmail.com> wrote:

> On Apr 22, 5:46 am, "petethebl...@googlemail.com"
> <petethebl...@googlemail.com> wrote:
>> On 21 Apr, 22:40, "Paul Lautman" <paul.laut...@btinternet.com> wrote:
>>
>> > Confidential!!! It will be a list of fields with data types, sizes
>> and field
>> > names. That is what all tables look like. How the !"!"££ can it be
>> > confidential!
>>
>> That's what I was trying to say, but more politely.
>
> Well, if we have a database that's available to the public through a
> website, and some entreprising hacker wants to do an SQL injection,
> they would get quite a leg up if they could look up the exact names of
> our fields and tables.
>
> Of course we are taking every precaution to prevent SQL injections and
> other hacking attemps, and not broadcasting table definitions on the
> internet is part of that
>
> As far as confidential, saying that no table definition can be
> confidential because it's made up of the same parts ( tables and
> fields ) as any other database is like saying all programs are the
> same because they're all made up of vairable and operators. It's the
> arrangement of the parts that adds value. Surely, you would agree that
> certain database structures are better than others for particular
> problems. Or all they all the same? If they aren't the same, then a
> business person who invested money to get a better data model might
> want to protect their investment.
>
> Why couldn't Microsoft release the code for Windows? After all, it's
> made up of the same parts as any other program. And I'm not talking
> security wise, but competition-wise -- for example, not giving OS
> providers a leg up in creating platforms to run software written to
> run with the Windows API.
>
> Suppose we have a business model, expressed as a database, that is
> superior to our competitions', and that allows us to ship products
> faster for less money. Why would we want to then give our model to our
> competition? We spent a lot of time and money building it; it doesn't
> make business sense to give it to your competition for free.

If the data/table definition is THAT sensitive, and security necessarily
THAT tight, I cannot imagine there is no money for an 'expert' reasonably
savvy enough to troubleshoot a simple unique index problem. If there's an
attempt at security at database level by people who can't even do this,
I'd say it's almost certainly doomed to fail or at least have a few holes.

In short, if you cannot 'broadcast' the table definition here, and my
simple example demonstrated that MySQL has no problem with a UNIQUE key on
multiple fields, we cannot help you any further, apart from wildly
guessing.

My first guess (and almost certainly the correct one) is:
- the UNIQUE is NOT defined properly, or there are more then 1 UNIQUE keys
defined on the same table.

My second and final farfetched guess is:
- client-id is erroneously defined as float and there are float troubles
(in which case not security but mere shame client-id was defined as a
float would prevent me from posting the table definition).

No more guesses will be made from here on by me. Without a table
definition, I leave you to the people with better crystal balls.
--
Rik Wasmus

On Tue, 22 Apr 2008 12:29:28 -0700 (PDT),
lawpoop@gmail.com wrote:

>Well, if we have a database that's available to the public through a
>website, and some entreprising hacker wants to do an SQL injection,
>they would get quite a leg up if they could look up the exact names of
>our fields and tables.

They could try to query the INFORMATION_SCHEMA first,
or use SHOW CREATE ... and explore from there.
--
( Kees
)
c[_] Suburbia: where they tear out the trees & then name streets after them. (#78)