Another question for the database gurus out there
-
Well my last post with a database question went well, I'll try another.
Is there a way to automatically generate a unique and random primary key, in the database? I know all about auto increment, but that leaves the problem that a user can see "getinfo.php?id=122" and guess that "getinfo.php?id=121" will work also. I want the id's to be random integers (32 bit int will be more than sufficient, database will have at most about 1000 rows), so that the user can't just guess another number. But the data I'm trying to conceal isn't confidential or sensitive in any way, so requiring users to create a profile and log in is overkill.
I've been Googling the subject and there doesn't seem to be a way to do this in the database, which seems odd (I mean, it seems like it would be a common problem).
I was thinking about just taking the last 8 hex digits off of the hex string returned by php code "md5(microtime() . mt_rand());", and convert to an int. There is a very small chance of generating the same value (theoretically, 1 in 2^32), but I can solve that programmatically as there is only going to be one user creating rows (i.e. no concern for transactions).
So... I have a decent pragrammatic solution, but I'd like to use the built-in database solution if possible. Anyone know if this is possible? Oh, and I'm using everyone's favorite database, MySQL. :)
-
Why do you need this functionality at all? I pray that this isn't supposed to be some kind of security, because if so, you need to revisit your entire plan.
I can't envision a system in which random numbers in the url are fundamentally better than sequential numbers in the URL.
-
Even if you use a random ID it won't be secure, someone might just
google "site:yoursite.com inurl:getinfo.php". Google seems to have a
knack for finding stuff website owners didn't want people to see :p
-
The random number method is not really a good idea since it's just another example of "security by obscurity" which by itself is dangerous to rely on. I just had to deal with a situation like this in an application I was writing. To prevent users from 'surfing' from one record to another I simply encrypt the url parameters and then convert them to a base64 ascii string to put into the URL.
Of course if you need real tight security, then as you said you would need some sort of authentication method to be sure someone doesn't look at at a record they are not authorized to see.
Dan
-
To clarify, I am fully aware of the fact that this is "security through obscurity". This is for a small hobby website, and the data isn't private, sensitive, or confedential. If someone sees a page that is not meant for them, it is not a big deal. I'd just rather it not happen.
But the reason I'm doing this is beside the point... I'd just like to know if there is anyway to make the database assign a unique random value to the private key when creating a record, or if I will have to write code to generate the id and make sure that it isn't in use. I would rather not reinvent the wheel if there is already a better method built into the database.
-
You could do that on a database system that supports triggers, stored
procedures and some kind of programming language for that (AFAIK, in
MySQL 5.0 stored procedures are pure SQL, I don't see how that would
help).
But I see no reason to do it in the database, it's probably easier to do in the application.
-
@kipthegreat said:
To clarify, I am fully aware of the fact that this is "security through obscurity". This is for a small hobby website, and the data isn't private, sensitive, or confedential. If someone sees a page that is not meant for them, it is not a big deal. I'd just rather it not happen.
But the reason I'm doing this is beside the point... I'd just like to know if there is anyway to make the database assign a unique random value to the private key when creating a record, or if I will have to write code to generate the id and make sure that it isn't in use. I would rather not reinvent the wheel if there is already a better method built into the database.Ok, to answer your direct question, no you cannot specify that the database generate a unique random number for your primary key field. It's quite possible to have the database assign a random number to a primary key field, however, since most database servers will also allow you to insert an item while specifying the primary key -- just make sure your primary key field isn't set to autoincriment. Most database servers supply a rand() function that will generate a pseudo-random number from 0 to 1, which you can use to generate a random number between any range of values. Typically, you'd use some algorthim like FLOOR(i + RAND() * (j - i). to generate a number between i and j. Of course, you have to watch out for number clobbers... after all, if you insert a primary key that already exists, your database server is gonna complain loudly. It might even 'splode.
I must stress, however, that if you use a random primary key rather than an autoincrement primary key, your code deserves to be posted to this website Monday through Friday. Never do it, it's bad database design, and as has been mentioned, security through obscurity.
If you really want to hide the user ID somehow, you can very easily do that without resorting to WTF trickery like random primary key numbers. First, don't put it the user id in a query string at all -- pass it via cookies, or better yet, store it in a server-side session variable that has a short expiration time. Thus even if Random Q. Hacker finds your server-side session id number, it'll be invalid. If you've never heard of sessions, here are some tutorials for common server-side technologies:
PHP: http://www.php.net/session
ASP.NET: http://msdn2.microsoft.com/en-us/library/87069683.aspx
JSP: http://www.jsptut.com/Sessions.jsp
-
Random number routines built into databases are often pretty bad. If you want to go that route, generate the random number in the app as the record is being created. Look in the cryptography package of whatever language you use. There will usually be a really good random number generator in there.
As for no databases providing a random autonumber -- there is at least one exception. Lowly Microft Jet (made popular in Access) has one.
Another option is to create a GUID. Windows provides an API to get one and most languages have an easy way to get to it (like C#'s "Guid.NewGuid()"). If you have to use the API, it's CoCreateGUID from OLE32.dll. It looks like you use PHP, so look at http://us3.php.net/uniqid
-
@jsmith said:
Another option is to create a GUID. Windows provides an API to get one and most languages have an easy way to get to it (like C#'s "Guid.NewGuid()"). If you have to use the API, it's CoCreateGUID from OLE32.dll. It looks like you use PHP, so look at http://us3.php.net/uniqid
Not sure that MySQL doesn't support UUID columns. Seem to remember having to do a narrrsty stored procedure hack to get Postgres to do uuids when migrating some particularly unlovely stuff from MySQL to Postgres.
Ah, yes, it does.
[code]
SELECT UUID()
[/code]
Looks like it returns a string rather than a raw 128 bit number, so you'll probably be wanting to use that 5000-line Java hex converter (or PHP equivalent). Or something.
Doesn't work with replication, but who gives a fuck, right? Using replication on MySQL is like entering a grand prix in a Reliant Robin :)
Simon
-
@Whiskey Tango Foxtrot said:
If you really want to hide the user ID somehow, you can very easily do that without resorting to WTF trickery like random primary key numbers. First, don't put it the user id in a query string at all -- pass it via cookies, or better yet, store it in a server-side session variable that has a short expiration time. Thus even if Random Q. Hacker finds your server-side session id number, it'll be invalid. If you've never heard of sessions, here are some tutorials for common server-side technologies
You're right, external download managers are an abomination unto god and should be forbidden.
-
@masklinn said:
@Whiskey Tango Foxtrot said:
If you really want to hide the user ID somehow, you can very easily do that without resorting to WTF trickery like random primary key numbers. First, don't put it the user id in a query string at all -- pass it via cookies, or better yet, store it in a server-side session variable that has a short expiration time. Thus even if Random Q. Hacker finds your server-side session id number, it'll be invalid. If you've never heard of sessions, here are some tutorials for common server-side technologies
You're right, external download managers are an abomination unto god and should be forbidden.
Um.... huh? What do you mean?
-
@Whiskey Tango Foxtrot Over said:
@masklinn said:
@Whiskey Tango Foxtrot said:
If you really want to hide the user ID somehow, you can very easily do that without resorting to WTF trickery like random primary key numbers. First, don't put it the user id in a query string at all -- pass it via cookies, or better yet, store it in a server-side session variable that has a short expiration time. Thus even if Random Q. Hacker finds your server-side session id number, it'll be invalid. If you've never heard of sessions, here are some tutorials for common server-side technologies
You're right, external download managers are an abomination unto god and should be forbidden.
Um.... huh? What do you mean?
Dunno, maybe that your sadistic cookie or session based schemes to take over the intarweb basically prevent anyone from using external download managers (such as Getright or Flashget) on websites implementing it?
-
@masklinn said:
@Whiskey Tango Foxtrot Over said:
@masklinn said:
@Whiskey Tango Foxtrot said:
If you really want to hide the user ID somehow, you can very easily do that without resorting to WTF trickery like random primary key numbers. First, don't put it the user id in a query string at all -- pass it via cookies, or better yet, store it in a server-side session variable that has a short expiration time. Thus even if Random Q. Hacker finds your server-side session id number, it'll be invalid. If you've never heard of sessions, here are some tutorials for common server-side technologies
You're right, external download managers are an abomination unto god and should be forbidden.
Um.... huh? What do you mean?
Dunno, maybe that your sadistic cookie or session based schemes to take over the intarweb basically prevent anyone from using external download managers (such as Getright or Flashget) on websites implementing it?
Er, I still don't know what you're getting at. Isn't the point of this to ensure that folks can't get access to something they're not allowed to have access to? Wouldn't it be a Good Thing (tm) to prevent unauthorized leeching?
-
The problem is that it also prevents authorized leeching because using non-standard authentication makes it impossible for any user agent aside from the one initiating the session to initiate the download. Use case:
I go to http://www.example.com/, log in, and get a list of files. I pick a file, http://www.example.com/my-files/foo.zip, to download. Now, foo.zip is a 200-meg zip file full of simulation data, and I'm on an unreliable connection, so I want to use my download manager to deal with automatically resuming when the download fails, so I copy and paste the URL into getright, or filezilla, or....
Except, instead of starting the download, I get an authorization required error -- or worse, an HTML page (status 200 OK) with the login form.
-
You could just make some two-way obfuscation routine - e.g. multiply the ID by a given number, then convert the digits to letters using an arbitrary hash table.
-
@Whiskey Tango Foxtrot Over said:
Er, I still don't know what you're getting at. Isn't the point of this to ensure that folks can't get access to something they're not allowed to have access to? Wouldn't it be a Good Thing (tm) to prevent unauthorized leeching?
May you explain how not being able to use a software I paid for to make my life easier when downloading stuff I'm authorized to access is a good thing?
My whole point is that with your stupid implementation, even if I am allowed to download files as far as authentification goes I am unable to download them as I usually do (which is through an external download software).
-
@masklinn said:
@Whiskey Tango Foxtrot Over said:
Er, I still don't know what you're getting at. Isn't the point of this to ensure that folks can't get access to something they're not allowed to have access to? Wouldn't it be a Good Thing (tm) to prevent unauthorized leeching?
May you explain how not being able to use a software I paid for to make my life easier when downloading stuff I'm authorized to access is a good thing?
My whole point is that with your stupid implementation, even if I am allowed to download files as far as authentification goes I am unable to download them as I usually do (which is through an external download software).
Server-side sessions cannot be considered stupid, they're rapidly becoming the defacto standard. Webmasters can always help support download management by using longer-expiration times for their sessions, or better yet, using a different type of session for authorized download than for accessing the rest of the website. If your download manager can't send a simple cookie to the website with a server session id, you wasted your money.
Besides, how did we get on this subject? File sharing is hardly the end-all be-all of web application creation.
-
@Whiskey Tango Foxtrot Over said:
@masklinn said:
@Whiskey Tango Foxtrot Over said:
Er, I still don't know what you're getting at. Isn't the point of this to ensure that folks can't get access to something they're not allowed to have access to? Wouldn't it be a Good Thing (tm) to prevent unauthorized leeching?
May you explain how not being able to use a software I paid for to make my life easier when downloading stuff I'm authorized to access is a good thing?
My whole point is that with your stupid implementation, even if I am allowed to download files as far as authentification goes I am unable to download them as I usually do (which is through an external download software).
Server-side sessions cannot be considered stupid, they're rapidly becoming the defacto standard.
Not for downloading content, you dummy
@Whiskey Tango Foxtrot Over said:Webmasters can always help support download management by using longer-expiration times for their sessions, or better yet, using a different type of session for authorized download than for accessing the rest of the website. If your download manager can't send a simple cookie to the website with a server session id, you wasted your money.
Pray tell, how the hell could my download manager use a cookie that has been created in the cookie repository of the browser
God damn it, everything a download manager gets is an URL, always, it has no reason to even try (and it shouldn't anyway, that would be a security breach) to retrieve information personal to the browser, on top of the fact that there is more than half a dozen browsers with different cookiejar implementations on every single platform out there.
-
SmartDownload, GetRight, NetAnts, and Download Accelerator all support passing cookies. I'm sorry if secure systems offends your sensibilities, but I'd rather have secure web applications.
-
Do they support cookie passing from any browser, or only ones they've specifically researched and reverse-engineered the API or cookie store format of? Can GetRight extract cookies from my elinks session? What about w3m?
-
@Whiskey Tango Foxtrot Over said:
SmartDownload, GetRight, NetAnts, and Download Accelerator all support passing cookies. I'm sorry if secure systems offends your sensibilities, but I'd rather have secure web applications.
Passing cookies from Opera or Firefox when they can't even manage to integrate with/in Firefox or Opera in the first place? Who the flying hell are you kidding?
(and calling "secure" a system in which your download manager has to have knowledge of the inner workings and implementation of your current browser as well as access to it's private data is... a bit farfetched... in my opinion)
-
@masklinn said:
(and calling "secure" a system in which your download manager has to have knowledge of the inner workings and implementation of your current browser as well as access to it's private data is... a bit farfetched... in my opinion)
First, no cookie can ever be considered "private" information. It's unsecure plain text, just like HTTP. Second, I don't give a whit about the security of the browser, anyway. I'm only talking about the security of the web application. The best means of securing a web application in an inherently unsecure and stateless environment like the web is to keep all the important stuff like user id, password, and any other important state information on the server and only expose a session identifier to the unsecure web. Even then, you run the risk of a session jacking, which is why you're supposed to keep the session timeout very short. Nearly every web application that exposes large file downloads (the only downloads that are an issue for download managers to tackle) exposes a longer session specifically for the download, or secures the downloaded files differently from the rest of the web application.
As for browser integration, Opera, Firefox, and IE all store their cookies in specific, easily read ways. If your download manager is poorly written and incapable of reading them, don't whine to me about it.
BTW, this has been a fun troll feeding, but this is my last off-topic post to this thread.
-
@kipthegreat said:
Well my last post with a database question went well, I'll try another.
Is there a way to automatically generate a unique and random primary key, in the database? I know all about auto increment, but that leaves the problem that a user can see "getinfo.php?id=122" and guess that "getinfo.php?id=121" will work also. I want the id's to be random integers (32 bit int will be more than sufficient, database will have at most about 1000 rows), so that the user can't just guess another number. But the data I'm trying to conceal isn't confidential or sensitive in any way, so requiring users to create a profile and log in is overkill.
I've been Googling the subject and there doesn't seem to be a way to do this in the database, which seems odd (I mean, it seems like it would be a common problem).
I was thinking about just taking the last 8 hex digits off of the hex string returned by php code "md5(microtime() . mt_rand());", and convert to an int. There is a very small chance of generating the same value (theoretically, 1 in 2^32), but I can solve that programmatically as there is only going to be one user creating rows (i.e. no concern for transactions).
So... I have a decent pragrammatic solution, but I'd like to use the built-in database solution if possible. Anyone know if this is possible? Oh, and I'm using everyone's favorite database, MySQL. :)Although I don't personally like using Guids, they may solve your issue... I don't know if the database you are using supports this, but MS SQL Server (including it's free version SQL Express) does...
Inside SQL Server, there's a NewID() function that will generate a random Guid...so you can insert into yor table(
Insert Tablename(PK, <other data fields>)
Values(NewID(), <Other Data Values>)
-
I would thought the same thing you did: calculate an MD5 hash and use that. I would hash the data rather than the current time however. That just seems more logical, (but really doesn't make a difference.)
As far as the cookie discussion goes, it would be trivial to pass the "cookie" in the URL.
http://www.example.org/download/d80ac87e2c1bf882bc47084de96bd8c4/files.zip can be easily rewritten into /download.php?auth=d80ac87e2c1bf882bc47084de96bd8c4. That will work with any browser/download manager.
