How to protect primary key value on a web page?

I want to bounce some ideas off of MySQL developers that use it for web
development. Maybe I'm a little paranoid, but when dealing with the
Internet, I want to make my web app as secure as possible. I'm hoping some
of you can offer me some ideas in this respect.

I am building a web application that uses MySQL 5.5 with Innodb tables and
I don't want the user to see the actual primary key value on the web page.
The primary key could be the cust_id, bill_id etc and is usually auto
increment. This primary key can appear in the url and will be used to pull
up a record and display it on the web page.

So I need some efficient way of 'cloaking' the real primary key so a hacker
won't try to generate random values to access info he shouldn't have access
to. How do most web sites handle this?

I thought of using UUID_Short() for the primary key instead of an auto-inc,
and this isn't really random. It generates near sequential numbers based on
time.

So I need a way of encrypting the cust_id before sending it to the web
page. The user can bookmark this page in his browser so I need to be able
to decrypt it back to the real cust_id to retrieve the data. Doing the
encryption and decryption is easy enough for me to do on the web server.

I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except
the string is very long at 64
characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string.

Another alternative is to store an MD5 hash value of Cust_Id in the table
under a different column "Cust_Id_Hash" and display that on the web
page. So the table joins would still use Cust_Id and Cust_Id_Hash would be
used only as a lookup when communicate with the web page. But Innodb's
ability to store large random strings will slow down inserts and will
consume more disk space.

What is the best way to solve the problem? I don't want to re-invent the
wheel because I'm sure this problem has been solved by other web
developers. Maybe an efficient solution is staring me in the face, so I'm
open to some suggestions. :-)

TIA
Mike


--
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: How to protect primary key value on a web page?

--90e6ba61527e18dcd4049e25246e
Content-Type: text/plain; charset=ISO-8859-1

Hi there,
Yes I think its actually a pattern a few hundreds million sites solved
already :)
And any way to encrypt (scramble)the http get string would do. But my
question is , are you afraid of sql injection? How do fear your db would be
violated?
On Mar 10, 2011 6:13 PM, "mos" wrote:
> I want to bounce some ideas off of MySQL developers that use it for web
> development. Maybe I'm a little paranoid, but when dealing with the
> Internet, I want to make my web app as secure as possible. I'm hoping some

> of you can offer me some ideas in this respect.
>
> I am building a web application that uses MySQL 5.5 with Innodb tables and

> I don't want the user to see the actual primary key value on the web page.

> The primary key could be the cust_id, bill_id etc and is usually auto
> increment. This primary key can appear in the url and will be used to pull

> up a record and display it on the web page.
>
> So I need some efficient way of 'cloaking' the real primary key so a
hacker
> won't try to generate random values to access info he shouldn't have
access
> to. How do most web sites handle this?
>
> I thought of using UUID_Short() for the primary key instead of an
auto-inc,
> and this isn't really random. It generates near sequential numbers based
on
> time.
>
> So I need a way of encrypting the cust_id before sending it to the web
> page. The user can bookmark this page in his browser so I need to be able
> to decrypt it back to the real cust_id to retrieve the data. Doing the
> encryption and decryption is easy enough for me to do on the web server.
>
> I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except

> the string is very long at 64
> characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string.
>
> Another alternative is to store an MD5 hash value of Cust_Id in the table
> under a different column "Cust_Id_Hash" and display that on the web
> page. So the table joins would still use Cust_Id and Cust_Id_Hash would be

> used only as a lookup when communicate with the web page. But Innodb's
> ability to store large random strings will slow down inserts and will
> consume more disk space.
>
> What is the best way to solve the problem? I don't want to re-invent the
> wheel because I'm sure this problem has been solved by other web
> developers. Maybe an efficient solution is staring me in the face, so I'm
> open to some suggestions. :-)
>
> TIA
> Mike
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>

--90e6ba61527e18dcd4049e25246e--

Re: How to protect primary key value on a web page?

--------------enig00842967BF3D67D3C18BAE77
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable


Am 10.03.2011 18:10, schrieb mos:

> I am building a web application that uses MySQL 5.5 with Innodb tables =
and I don't want the user to see the actual
> primary key value on the web page. The primary key could be the cust_id=
, bill_id etc and is usually auto increment.
> This primary key can appear in the url and will be used to pull up a re=
cord and display it on the web page.

> So I need some efficient way of 'cloaking' the real primary key so a ha=
cker won't try to generate random values to
> access info he shouldn't have access to. How do most web sites handle t=
his?

the most sites will handle this by checking permissions
security by obscurity is simple crap

if i have access to record 738 and get z39 by changing the url
your application is simply broken


--------------enig00842967BF3D67D3C18BAE77
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk15HEcACgkQhmBjz394AnmRPgCeNLip2yoHUvP09r8vEW4H R/ZN
EVwAnRZQLb/XjMco1cfXwB20+bH4xRq0
=TkNH
-----END PGP SIGNATURE-----

--------------enig00842967BF3D67D3C18BAE77--

Re: How to protect primary key value on a web page?

On Thursday 10 March 2011 11:45:27 am Reindl Harald wrote:
> Am 10.03.2011 18:10, schrieb mos:
> > I am building a web application that uses MySQL 5.5 with Innodb tables
> > and I don't want the user to see the actual primary key value on the web
> > page. The primary key could be the cust_id, bill_id etc and is usually
> > auto increment. This primary key can appear in the url and will be used
> > to pull up a record and display it on the web page.
> >
> > So I need some efficient way of 'cloaking' the real primary key so a
> > hacker won't try to generate random values to access info he shouldn't
> > have access to. How do most web sites handle this?
>
> the most sites will handle this by checking permissions
> security by obscurity is simple crap
>
> if i have access to record 738 and get z39 by changing the url
> your application is simply broken

I think the original poster knows/suspects his application is broken and thats
why he's asking.

I think he has a case where he allows a user to edit their own records and
doesn't have the ability to require a username/password from them,

I have a similar situation. What I do is store a random number in their
record, which I also include in the url. Access to the record is gained by
the combination of id, and tag. Just a thought.


--

Take care and have fun,
Mike Diehl.

--
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: How to protect primary key value on a web page?

At 12:37 PM 3/10/2011, Claudio Nanni wrote:

>Hi there,
>Yes I think its actually a pattern a few hundreds million sites solved
>already :)

Great. How did they do it? :)

>And any way to encrypt (scramble)the http get string would do. But my
>question is , are you afraid of sql injection?

I'm using parameterized queries and validating user input so SQL injection
shouldn't be a problem.
I just don't want to give the hacker any more useful information than
necessary. Let's say I have a Document_Id column and the url is
www.mydocuments.com/public?docid=4

to retrieve document_id=4, I don't want someone to write a program to
retrieve all of my public documents and download them. I want them to go
through the user interface.
The private documents of course need a user name and password to access
them, but public documents do not require passwords.

So hashing or encrypting the id column will make the id's non-contiguous
and impossible to guess.

Mike

>How do fear your db would be violated?
>On Mar 10, 2011 6:13 PM, "mos"
><mos99@fastmail.fm> wrote:
> > I want to bounce some ideas off of MySQL developers that use it for web
> > development. Maybe I'm a little paranoid, but when dealing with the
> > Internet, I want to make my web app as secure as possible. I'm hoping some
> > of you can offer me some ideas in this respect.
> >
> > I am building a web application that uses MySQL 5.5 with Innodb tables and
> > I don't want the user to see the actual primary key value on the web page.
> > The primary key could be the cust_id, bill_id etc and is usually auto
> > increment. This primary key can appear in the url and will be used to pull
> > up a record and display it on the web page.
> >
> > So I need some efficient way of 'cloaking' the real primary key so a
> hacker
> > won't try to generate random values to access info he shouldn't have
> access
> > to. How do most web sites handle this?
> >
> > I thought of using UUID_Short() for the primary key instead of an
> auto-inc,
> > and this isn't really random. It generates near sequential numbers
> based on
> > time.
> >
> > So I need a way of encrypting the cust_id before sending it to the web
> > page. The user can bookmark this page in his browser so I need to be able
> > to decrypt it back to the real cust_id to retrieve the data. Doing the
> > encryption and decryption is easy enough for me to do on the web server.
> >
> > I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine except
> > the string is very long at 64
> > characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller string.
> >
> > Another alternative is to store an MD5 hash value of Cust_Id in the table
> > under a different column "Cust_Id_Hash" and display that on the web
> > page. So the table joins would still use Cust_Id and Cust_Id_Hash would be
> > used only as a lookup when communicate with the web page. But Innodb's
> > ability to store large random strings will slow down inserts and will
> > consume more disk space.
> >
> > What is the best way to solve the problem? I don't want to re-invent the
> > wheel because I'm sure this problem has been solved by other web
> > developers. Maybe an efficient solution is staring me in the face, so I'm
> > open to some suggestions. :-)
> >
> > TIA
> > Mike
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives:
> http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
> >


--
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: How to protect primary key value on a web page?

--------------enig4C722937B96FF8D678DC2B66
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 10.03.2011 21:09, schrieb mos:
> At 12:37 PM 3/10/2011, Claudio Nanni wrote:
>=20
>> Hi there,
>> Yes I think its actually a pattern a few hundreds million sites solved=
already :)
>=20
> Great. How did they do it? :)
>=20
>> And any way to encrypt (scramble)the http get string would do. But my =
question is , are you afraid of sql injection?
>=20
> I'm using parameterized queries and validating user input so SQL inject=
ion shouldn't be a problem.
> I just don't want to give the hacker any more useful information than n=
ecessary. Let's say I have a Document_Id
> column and the url is
> www.mydocuments.com/public?docid=3D4
>=20
> to retrieve document_id=3D4, I don't want someone to write a program to=
retrieve all of my public documents and
> download them. I want them to go through the user interface.
> The private documents of course need a user name and password to access=
them, but public documents do not require
> passwords.
>=20
> So hashing or encrypting the id column will make the id's non-contiguou=
s and impossible to guess.

sorry but this is foolish
leave the id in peace and add a colum with some checksum



--------------enig4C722937B96FF8D678DC2B66
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk15M0IACgkQhmBjz394AnnpRQCeLK8niGSLMHBLstwqX8kT FStA
ld0Ani5hEi67OCMJyvwu2zi3Gp8wtvji
=xbOP
-----END PGP SIGNATURE-----

--------------enig4C722937B96FF8D678DC2B66--

Re: How to protect primary key value on a web page?

--20cf303dd43e979a9d049e26aaba
Content-Type: text/plain; charset=ISO-8859-1

On Mar 10, 2011 9:13 PM, "mos" wrote:
>
> At 12:37 PM 3/10/2011, Claudio Nanni wrote:
>
>> Hi there,
>> Yes I think its actually a pattern a few hundreds million sites solved
already :)
>
>
> Great. How did they do it? :)
>
Please, google for me I am cooking right now :)

>
>> And any way to encrypt (scramble)the http get string would do. But my
question is , are you afraid of sql injection?
>
>
> I'm using parameterized queries and validating user input so SQL injection
shouldn't be a problem.
> I just don't want to give the hacker any more useful information than
necessary. Let's say I have a Document_Id column and the url is
> www.mydocuments.com/public?docid=4
>
> to retrieve document_id=4, I don't want someone to write a program to
retrieve all of my public documents and download them. I want them to go
through the user interface.
> The private documents of course need a user name and password to access
them, but public documents do not require passwords.
>
> So hashing or encrypting the id column will make the id's non-contiguous
and impossible to guess.
>
then you have the solution!
I actually I am not a GET lover for your same reasons, and I would just
store an handle in the cookie and keep all the state in a session on the
server.

> Mike
>
>> How do fear your db would be violated?
>> On Mar 10, 2011 6:13 PM, "mos" <
mos99@fastmail.fm> wrote:
>> > I want to bounce some ideas off of MySQL developers that use it for web
>> > development. Maybe I'm a little paranoid, but when dealing with the
>> > Internet, I want to make my web app as secure as possible. I'm hoping
some
>> > of you can offer me some ideas in this respect.
>> >
>> > I am building a web application that uses MySQL 5.5 with Innodb tables
and
>> > I don't want the user to see the actual primary key value on the web
page.
>> > The primary key could be the cust_id, bill_id etc and is usually auto
>> > increment. This primary key can appear in the url and will be used to
pull
>> > up a record and display it on the web page.
>> >
>> > So I need some efficient way of 'cloaking' the real primary key so a
hacker
>> > won't try to generate random values to access info he shouldn't have
access
>> > to. How do most web sites handle this?
>> >
>> > I thought of using UUID_Short() for the primary key instead of an
auto-inc,
>> > and this isn't really random. It generates near sequential numbers
based on
>> > time.
>> >
>> > So I need a way of encrypting the cust_id before sending it to the web
>> > page. The user can bookmark this page in his browser so I need to be
able
>> > to decrypt it back to the real cust_id to retrieve the data. Doing the
>> > encryption and decryption is easy enough for me to do on the web
server.
>> >
>> > I have tried Hex(AES_Encrypt(Cust_Id,'secret')) and this works fine
except
>> > the string is very long at 64
>> > characters. hex(DES_Encrypt(Cust_Id,'secret')) generates a smaller
string.
>> >
>> > Another alternative is to store an MD5 hash value of Cust_Id in the
table
>> > under a different column "Cust_Id_Hash" and display that on the web
>> > page. So the table joins would still use Cust_Id and Cust_Id_Hash would
be
>> > used only as a lookup when communicate with the web page. But Innodb's
>> > ability to store large random strings will slow down inserts and will
>> > consume more disk space.
>> >
>> > What is the best way to solve the problem? I don't want to re-invent
the
>> > wheel because I'm sure this problem has been solved by other web
>> > developers. Maybe an efficient solution is staring me in the face, so
I'm
>> > open to some suggestions. :-)
>> >
>> > TIA
>> > Mike
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives:
http://lists.mysql.com/mysql
>> > To unsubscribe: <
http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com>
http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>> >
>
>

--20cf303dd43e979a9d049e26aaba--

Re: How to protect primary key value on a web page?

--20cf303dd43e8425e5049e2713d9
Content-Type: text/plain; charset=ISO-8859-1

On Mar 10, 2011 9:23 PM, "Reindl Harald" wrote:
>
>
>
> Am 10.03.2011 21:09, schrieb mos:
> > At 12:37 PM 3/10/2011, Claudio Nanni wrote:
> >
> >> Hi there,
> >> Yes I think its actually a pattern a few hundreds million sites solved
already :)
> >
> > Great. How did they do it? :)
> >
> >> And any way to encrypt (scramble)the http get string would do. But my
question is , are you afraid of sql injection?
> >
> > I'm using parameterized queries and validating user input so SQL
injection shouldn't be a problem.
> > I just don't want to give the hacker any more useful information than
necessary. Let's say I have a Document_Id
> > column and the url is
> > www.mydocuments.com/public?docid=4
> >
> > to retrieve document_id=4, I don't want someone to write a program to
retrieve all of my public documents and
> > download them. I want them to go through the user interface.
> > The private documents of course need a user name and password to access
them, but public documents do not require
> > passwords.
> >
> > So hashing or encrypting the id column will make the id's non-contiguous
and impossible to guess.
>
> sorry but this is foolish
> leave the id in peace and add a colum with some checksum

Wordpress guys are also foolish?
They do not even encrypt.
And what's the difference between passing in a GET an encrypted Id or
passing another column with a checksum deriving from the Id?

--20cf303dd43e8425e5049e2713d9--

Re: How to protect primary key value on a web page?

--------------enig9041F8113B4425A500BE9E10
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable



Am 10.03.2011 21:56, schrieb Claudio Nanni:
> On Mar 10, 2011 9:23 PM, "Reindl Harald" wrote=
:

>>> So hashing or encrypting the id column will make the id's non-contigu=
ous
> and impossible to guess.
>>
>> sorry but this is foolish
>> leave the id in peace and add a colum with some checksum
>=20
> Wordpress guys are also foolish?

of course they are
look at their awful code
you will not really tell me that quality looks like wordpress?

> And what's the difference between passing in a GET an encrypted Id or
> passing another column with a checksum deriving from the Id?

what exactly do you not understand?

fecth the record by its primary key is pretty fast
decide the data-output by a checksum which is independent
to the key

how will you do this any other way?
you can not use hash functions because you can not revert them for
fetch the record, so you have to use obfusction you can revert to
the key and if you can do this anybody will sooner or later




--------------enig9041F8113B4425A500BE9E10
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk15PWoACgkQhmBjz394AnkOWQCglZDmL6cjW0Sbwg+XeON/ 9ZD0
o+wAoIYf+i+FM0faT3TugVCtRMSwUdQ3
=I8qb
-----END PGP SIGNATURE-----

--------------enig9041F8113B4425A500BE9E10--

Re: How to protect primary key value on a web page?

On 3/10/2011 12:10, mos wrote:
> I want to bounce some ideas off of MySQL developers that use it for web
> development. Maybe I'm a little paranoid, but when dealing with the
> Internet, I want to make my web app as secure as possible. I'm hoping
> some of you can offer me some ideas in this respect.
>
> I am building a web application that uses MySQL 5.5 with Innodb tables
> and I don't want the user to see the actual primary key value on the web
> page. The primary key could be the cust_id, bill_id etc and is usually
> auto increment. This primary key can appear in the url and will be used
> to pull up a record and display it on the web page.
> ...

You could follow some of the basic security designs already in use.

1) use https://

2) Don't worry about the URLs, worry about authenticating the requesting
user to the session to the data. Only allow the users access to what
they are supposed to have access to in the quantities they are allowed
to view it.

3) You could include the session identifier as part of the URL. Once the
session expires, that URL is now dead.

One of your worries was a BOT coming along and scraping off all of your
public files. That's pretty easy to catch if you actively monitor usage
patterns. Another way of doing that is to have two unique identifiers
for each data object, one is the sequential private number, the other is
the non-incremental (random or hash) value that you can expose via URL.
It's not really securing anything but it is making it harder for
random successes. If they fail to randomly find a valid value enough
times, you lock out that IP address.


However this really isn't a great topic for a database list as most of
solution to your problems reside in how you design your application.

Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
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: How to protect primary key value on a web page?

Hi.

On Thursday 10 Mar 2011 at 20:09 mos wrote:

[snip]

> Let's say I have a Document_Id column and the url is
> www.mydocuments.com/public?docid=4
> to retrieve document_id=4, I don't want someone to write a program to
> retrieve all of my public documents and download them. I want them to go
> through the user interface.

Leaving aside the silliness of making a document public then trying to stop
people downloading it, there is at least one common solution available to you
- Apache's mod_rewrite.

http://httpd.apache.org/docs/2.0/mod/mod_rewrite.html

Obviously this is dependant on you running Apache, but it is a simple and
common approach that will give you what you want.

You could also consider rate-limiting your application so that users who
request too many pages for your tastes (indicating a possible program) are
deliberately slowed down. Beware that this solution will likely have a
detrimental effect on search engine spiders, and therefore your site rankings.

However, neither of these solutions are appropriate for discussion on a MySQL
mailing list, and I agree with many of the other responses you have had - your
plan to do this by changing your database is pointless and misdirected.

Cheers,

Mark

--
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