Re: Random record identifiers in MySQL 5.0
Judah McAuley wrote: > Barney's solution is good but doesn't change the fact that you are > authenticating based on a piece of information that someone else can > grab (a cookie). I understand that one of your requirements is that > people not have to use a username and password to log in every time. > That just means that a level of insecurity is built into your > application. That can be mitigated to some extent though. *nods* I agree it isn't Fort Knox, but it's enough security for the information provided. > Make sure that cookie reads and writes take place over SSL. That way > people can't easily grab the cookie value over the wire. I don't have access to SSL on my hosting server. So that isn't an option for me. > Include, as > Barney mentioned, an expiration date and keep it short. Change the > value of the cookie each time the user visits. That way if I do steal > your cookie, I can only use it for a short time and when the real user > goes to visit, they won't have the correct cookie anyore and will have > to log in, therefore invalidating the old cookie. *nods* I keep it for a day. With the level of traffic and return times for people, that seems to work the best. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314497 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
> When you say "You know the secret key", are you referring to the site or > the user? Sorry, I wasn't very clear. The site knows the key, so only the site can validate the contents of cookies. > Either way, how would this stop someone from copying the key and using > it on another computer? Yes, they could certainly do that. But they can't change the values or it won't validate when it gets server-side. Which means you can use incredibly tight expirations (or use single-use IDs) to prevent attackers from being able to steal and use the cookies. Because of the secret key, attackers can't create their own modified cookies either. It's not foolproof, of course, since if you're really on the ball you can grab a cookie and use it before the person it was sent to gets a chance to use it. If you have that in place, it's easy to do session locking if an attack is detected. I.e. if you double-use an ID, no cookies for that account are accepted and all users have to log in again manually. Like anything else, it's not foolproof. Security never is. Even x509 is vulnerable to private key theft. cheers, barneyb On Tue, Oct 28, 2008 at 1:22 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > When you say "You know the secret key", are you referring to the site or > the user? > > Either way, how would this stop someone from copying the key and using > it on another computer? > > Let's say the phrase "SecretKey" was the secret key.. So my cookie would > look like this.. > > PVector:1/1/2010:7f98w7f9f98wfh9wh6f976h326 > > So... What is to stop someone from taking that cookie and using it on > their machine? This doesn't seem to increase security at all. > > > Barney Boisvert wrote: >> create your cookie like this: >> >> #userId#:#expirationDate#:#hash(userId & expirationDate & yourSecretKey)# >> >> Then you can ensure the cookie came from you and that it hasn't been >> manipulated, because only you can properly create the hash (because >> only you know the secret key). >> >> cheers, >> barneyb >> >> On Tue, Oct 28, 2008 at 1:10 PM, Phillip M. Vector >> <[EMAIL PROTECTED]> wrote: >>> So how do you suggest I validate the cookie without requiring User input >>> (invalidating the purpose of the cookie in the first place)? >>> >>> Barney Boisvert wrote: A spin attack is when you manipulate some form of captured user input. It's usually a number, so the name comes from spinning a numeric dial. Any user input, which includes cookies, has to be validated. If you just trust the cookie, anyone who steals the cookie can impersonate the user. Even encrypting it doesn't help, because the attacker doesn't need the actual value, he just has to pass the cookie. On Tue, Oct 28, 2008 at 12:46 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > Perhaps you weren't reading it clearly. Allow me to explain. > > I give the UserID (in UUID form and encrypted) out when someone hits my > site. > > When a user has it, I load up that profile and they "log in" to the site. > > If a user doesn't have it, they need to log in with a username and > password. > > I fail to see why this is insecure. How do you suggest that I > authenticate that it's the correct person without any user input and > allowing them to log into the site from more then one computer/ip? > > and I'm not falimiar with a spin attack. What is that? > > Barney Boisvert wrote: >> WHAT You store a userId in a cookie and trust it Are you >> mad??? Numbers are as inherently secure as UUIDs - they're both >> simply identifiers. Authentication and authorization are where >> security happens. If an application is susceptible to spin attacks >> like that, I suppose that a UUID might assist to some degree, but much >> better to just prevent the spin attack. >> >> cheers, >> barneyb >> >> On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector >> <[EMAIL PROTECTED]> wrote: >>> Oh.. I have that as well. But take for example the UserID that I store >>> as a cookie to someone else based on the UserID field. >>> >>> It's easy to change a cookie to a 1 and hope to get admin access. >>> >>> It's harder to figure out someone elses ID. :) >>> >>> and yeah, I can set it to the IP and so on, but honestly, using a UUID >>> is allot more secure then auto increase. >>> >>> Matt Quackenbush wrote: On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: > The only thing I've noticed in using that is that you can guess the > next > number. > > If you have a URL string of id set to 7, I've always tried manually > typing in 6 and seeing what happens. Sometimes, 5. :) > > That's what permission checking in your application is for. :-) >>> >> >> > >
Re: Random record identifiers in MySQL 5.0
Well, I set the cookie every page load, but you are saying.. If the cookie doesn't match up with what the last "Counter" I used (I.e. if the cookie shows a counterID of 5 and it should be a 6), then redirect to the login. Right? If that's correct, once they stop using the page, what's to prevent someone from grabbing the last cookie and using it? Also, I can't see how it makes it easy to handle multiple tabs to the site feasible. If I'm mistaken in understand what you are suggesting, please explain. I'm honestly curious how I can do this any better then I am already. Barney Boisvert wrote: > The simplest mechanism is to only allow a cookie to be used once, and > then reset it each request. You get the cookie, ensure it's valid, > ensure the id hasn't been used before, create a new cookie, set it, > and then process the request. If the cookie isn't valid or the id has > been used, you clear the cookie and redirect to the login form. You > can do the same thing but only check the cookie if the CF session > isn't already considered authenticated. That'll reduce the amount of > checking you have to do, but significantly reduces the security. > > And unless you're on SSL it's easy to grab cookies without machine > access. Even with SSL it can still be done in some cases. > > cheers, > barneyb > > On Tue, Oct 28, 2008 at 12:56 PM, Phillip M. Vector > <[EMAIL PROTECTED]> wrote: >> If you managed to copy a cookie to your machine, then either 1 of 2 >> things happened. >> >> 1) I gave you permission to do so and therefore, I understand the >> concept that I'm giving you my ID on the site basically. >> 2) You took it without me knowing. This would involve you accessing my >> computer in some way and if I don't know you well enough to trust you, >> then you aren't going to access my machine. Anyway, you can just log >> into the site from my machine anyway. :) >> >> I should also point out that there is a "Logout" function that removes >> the cookie. So people who are security conscious can log out if needed. >> >> Either way, how do you suggest I "authenticate" a person with the cookie >> to make sure it's really the proper user without having the user >> re-login to the site? I suppose I can have them relog in if their IP >> changes, but IP's can be faked as well. >> >> Judah McAuley wrote: >>> What if I copied your cookie to my machine? I go to your site, it >>> checks to see if I have a cookie, I do, so it grabs the encrypted UUID >>> value in that cookie, checks it against your db, matches your record, >>> then logs me in as you. >>> >>> I don't have to know the value of the UUID. It doesn't matter that it >>> is encrypted. I only have to have the same value that you do. >>> >>> Judah >>> >>> On Tue, Oct 28, 2008 at 12:46 PM, Phillip M. Vector >>> <[EMAIL PROTECTED]> wrote: Perhaps you weren't reading it clearly. Allow me to explain. I give the UserID (in UUID form and encrypted) out when someone hits my site. When a user has it, I load up that profile and they "log in" to the site. If a user doesn't have it, they need to log in with a username and password. I fail to see why this is insecure. How do you suggest that I authenticate that it's the correct person without any user input and allowing them to log into the site from more then one computer/ip? and I'm not falimiar with a spin attack. What is that? Barney Boisvert wrote: > WHAT You store a userId in a cookie and trust it Are you > mad??? Numbers are as inherently secure as UUIDs - they're both > simply identifiers. Authentication and authorization are where > security happens. If an application is susceptible to spin attacks > like that, I suppose that a UUID might assist to some degree, but much > better to just prevent the spin attack. > > cheers, > barneyb > > On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector > <[EMAIL PROTECTED]> wrote: >> Oh.. I have that as well. But take for example the UserID that I store >> as a cookie to someone else based on the UserID field. >> >> It's easy to change a cookie to a 1 and hope to get admin access. >> >> It's harder to figure out someone elses ID. :) >> >> and yeah, I can set it to the IP and so on, but honestly, using a UUID >> is allot more secure then auto increase. >> >> Matt Quackenbush wrote: >>> On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: >>> The only thing I've noticed in using that is that you can guess the next number. If you have a URL string of id set to 7, I've always tried manually typing in 6 and seeing what happens. Sometimes, 5. :) >>> That's what permission checking in your application is for. :-) >>> >>> >>> >>> >> > > ~
Re: Random record identifiers in MySQL 5.0
I would add the following: Barney's solution is good but doesn't change the fact that you are authenticating based on a piece of information that someone else can grab (a cookie). I understand that one of your requirements is that people not have to use a username and password to log in every time. That just means that a level of insecurity is built into your application. That can be mitigated to some extent though. Make sure that cookie reads and writes take place over SSL. That way people can't easily grab the cookie value over the wire. Include, as Barney mentioned, an expiration date and keep it short. Change the value of the cookie each time the user visits. That way if I do steal your cookie, I can only use it for a short time and when the real user goes to visit, they won't have the correct cookie anyore and will have to log in, therefore invalidating the old cookie. Not fool proof, but better. Judah On Tue, Oct 28, 2008 at 1:14 PM, Barney Boisvert <[EMAIL PROTECTED]> wrote: > create your cookie like this: > > #userId#:#expirationDate#:#hash(userId & expirationDate & yourSecretKey)# > > Then you can ensure the cookie came from you and that it hasn't been > manipulated, because only you can properly create the hash (because > only you know the secret key). > > cheers, > barneyb ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314493 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
The simplest mechanism is to only allow a cookie to be used once, and then reset it each request. You get the cookie, ensure it's valid, ensure the id hasn't been used before, create a new cookie, set it, and then process the request. If the cookie isn't valid or the id has been used, you clear the cookie and redirect to the login form. You can do the same thing but only check the cookie if the CF session isn't already considered authenticated. That'll reduce the amount of checking you have to do, but significantly reduces the security. And unless you're on SSL it's easy to grab cookies without machine access. Even with SSL it can still be done in some cases. cheers, barneyb On Tue, Oct 28, 2008 at 12:56 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > If you managed to copy a cookie to your machine, then either 1 of 2 > things happened. > > 1) I gave you permission to do so and therefore, I understand the > concept that I'm giving you my ID on the site basically. > 2) You took it without me knowing. This would involve you accessing my > computer in some way and if I don't know you well enough to trust you, > then you aren't going to access my machine. Anyway, you can just log > into the site from my machine anyway. :) > > I should also point out that there is a "Logout" function that removes > the cookie. So people who are security conscious can log out if needed. > > Either way, how do you suggest I "authenticate" a person with the cookie > to make sure it's really the proper user without having the user > re-login to the site? I suppose I can have them relog in if their IP > changes, but IP's can be faked as well. > > Judah McAuley wrote: >> What if I copied your cookie to my machine? I go to your site, it >> checks to see if I have a cookie, I do, so it grabs the encrypted UUID >> value in that cookie, checks it against your db, matches your record, >> then logs me in as you. >> >> I don't have to know the value of the UUID. It doesn't matter that it >> is encrypted. I only have to have the same value that you do. >> >> Judah >> >> On Tue, Oct 28, 2008 at 12:46 PM, Phillip M. Vector >> <[EMAIL PROTECTED]> wrote: >>> Perhaps you weren't reading it clearly. Allow me to explain. >>> >>> I give the UserID (in UUID form and encrypted) out when someone hits my >>> site. >>> >>> When a user has it, I load up that profile and they "log in" to the site. >>> >>> If a user doesn't have it, they need to log in with a username and password. >>> >>> I fail to see why this is insecure. How do you suggest that I >>> authenticate that it's the correct person without any user input and >>> allowing them to log into the site from more then one computer/ip? >>> >>> and I'm not falimiar with a spin attack. What is that? >>> >>> Barney Boisvert wrote: WHAT You store a userId in a cookie and trust it Are you mad??? Numbers are as inherently secure as UUIDs - they're both simply identifiers. Authentication and authorization are where security happens. If an application is susceptible to spin attacks like that, I suppose that a UUID might assist to some degree, but much better to just prevent the spin attack. cheers, barneyb On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > Oh.. I have that as well. But take for example the UserID that I store > as a cookie to someone else based on the UserID field. > > It's easy to change a cookie to a 1 and hope to get admin access. > > It's harder to figure out someone elses ID. :) > > and yeah, I can set it to the IP and so on, but honestly, using a UUID > is allot more secure then auto increase. > > Matt Quackenbush wrote: >> On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: >> >>> The only thing I've noticed in using that is that you can guess the next >>> number. >>> >>> If you have a URL string of id set to 7, I've always tried manually >>> typing in 6 and seeing what happens. Sometimes, 5. :) >>> >>> >> That's what permission checking in your application is for. :-) >> >> >> >>> >> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314492 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
When you say "You know the secret key", are you referring to the site or the user? Either way, how would this stop someone from copying the key and using it on another computer? Let's say the phrase "SecretKey" was the secret key.. So my cookie would look like this.. PVector:1/1/2010:7f98w7f9f98wfh9wh6f976h326 So... What is to stop someone from taking that cookie and using it on their machine? This doesn't seem to increase security at all. Barney Boisvert wrote: > create your cookie like this: > > #userId#:#expirationDate#:#hash(userId & expirationDate & yourSecretKey)# > > Then you can ensure the cookie came from you and that it hasn't been > manipulated, because only you can properly create the hash (because > only you know the secret key). > > cheers, > barneyb > > On Tue, Oct 28, 2008 at 1:10 PM, Phillip M. Vector > <[EMAIL PROTECTED]> wrote: >> So how do you suggest I validate the cookie without requiring User input >> (invalidating the purpose of the cookie in the first place)? >> >> Barney Boisvert wrote: >>> A spin attack is when you manipulate some form of captured user input. >>> It's usually a number, so the name comes from spinning a numeric >>> dial. Any user input, which includes cookies, has to be validated. >>> If you just trust the cookie, anyone who steals the cookie can >>> impersonate the user. Even encrypting it doesn't help, because the >>> attacker doesn't need the actual value, he just has to pass the >>> cookie. >>> >>> On Tue, Oct 28, 2008 at 12:46 PM, Phillip M. Vector >>> <[EMAIL PROTECTED]> wrote: Perhaps you weren't reading it clearly. Allow me to explain. I give the UserID (in UUID form and encrypted) out when someone hits my site. When a user has it, I load up that profile and they "log in" to the site. If a user doesn't have it, they need to log in with a username and password. I fail to see why this is insecure. How do you suggest that I authenticate that it's the correct person without any user input and allowing them to log into the site from more then one computer/ip? and I'm not falimiar with a spin attack. What is that? Barney Boisvert wrote: > WHAT You store a userId in a cookie and trust it Are you > mad??? Numbers are as inherently secure as UUIDs - they're both > simply identifiers. Authentication and authorization are where > security happens. If an application is susceptible to spin attacks > like that, I suppose that a UUID might assist to some degree, but much > better to just prevent the spin attack. > > cheers, > barneyb > > On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector > <[EMAIL PROTECTED]> wrote: >> Oh.. I have that as well. But take for example the UserID that I store >> as a cookie to someone else based on the UserID field. >> >> It's easy to change a cookie to a 1 and hope to get admin access. >> >> It's harder to figure out someone elses ID. :) >> >> and yeah, I can set it to the IP and so on, but honestly, using a UUID >> is allot more secure then auto increase. >> >> Matt Quackenbush wrote: >>> On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: >>> The only thing I've noticed in using that is that you can guess the next number. If you have a URL string of id set to 7, I've always tried manually typing in 6 and seeing what happens. Sometimes, 5. :) >>> That's what permission checking in your application is for. :-) >>> >>> >>> >>> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314491 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Random record identifiers in MySQL 5.0
create your cookie like this: #userId#:#expirationDate#:#hash(userId & expirationDate & yourSecretKey)# Then you can ensure the cookie came from you and that it hasn't been manipulated, because only you can properly create the hash (because only you know the secret key). cheers, barneyb On Tue, Oct 28, 2008 at 1:10 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > So how do you suggest I validate the cookie without requiring User input > (invalidating the purpose of the cookie in the first place)? > > Barney Boisvert wrote: >> A spin attack is when you manipulate some form of captured user input. >> It's usually a number, so the name comes from spinning a numeric >> dial. Any user input, which includes cookies, has to be validated. >> If you just trust the cookie, anyone who steals the cookie can >> impersonate the user. Even encrypting it doesn't help, because the >> attacker doesn't need the actual value, he just has to pass the >> cookie. >> >> On Tue, Oct 28, 2008 at 12:46 PM, Phillip M. Vector >> <[EMAIL PROTECTED]> wrote: >>> Perhaps you weren't reading it clearly. Allow me to explain. >>> >>> I give the UserID (in UUID form and encrypted) out when someone hits my >>> site. >>> >>> When a user has it, I load up that profile and they "log in" to the site. >>> >>> If a user doesn't have it, they need to log in with a username and password. >>> >>> I fail to see why this is insecure. How do you suggest that I >>> authenticate that it's the correct person without any user input and >>> allowing them to log into the site from more then one computer/ip? >>> >>> and I'm not falimiar with a spin attack. What is that? >>> >>> Barney Boisvert wrote: WHAT You store a userId in a cookie and trust it Are you mad??? Numbers are as inherently secure as UUIDs - they're both simply identifiers. Authentication and authorization are where security happens. If an application is susceptible to spin attacks like that, I suppose that a UUID might assist to some degree, but much better to just prevent the spin attack. cheers, barneyb On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > Oh.. I have that as well. But take for example the UserID that I store > as a cookie to someone else based on the UserID field. > > It's easy to change a cookie to a 1 and hope to get admin access. > > It's harder to figure out someone elses ID. :) > > and yeah, I can set it to the IP and so on, but honestly, using a UUID > is allot more secure then auto increase. > > Matt Quackenbush wrote: >> On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: >> >>> The only thing I've noticed in using that is that you can guess the next >>> number. >>> >>> If you have a URL string of id set to 7, I've always tried manually >>> typing in 6 and seeing what happens. Sometimes, 5. :) >>> >>> >> That's what permission checking in your application is for. :-) >> >> >> >>> >> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314488 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
So how do you suggest I validate the cookie without requiring User input (invalidating the purpose of the cookie in the first place)? Barney Boisvert wrote: > A spin attack is when you manipulate some form of captured user input. > It's usually a number, so the name comes from spinning a numeric > dial. Any user input, which includes cookies, has to be validated. > If you just trust the cookie, anyone who steals the cookie can > impersonate the user. Even encrypting it doesn't help, because the > attacker doesn't need the actual value, he just has to pass the > cookie. > > On Tue, Oct 28, 2008 at 12:46 PM, Phillip M. Vector > <[EMAIL PROTECTED]> wrote: >> Perhaps you weren't reading it clearly. Allow me to explain. >> >> I give the UserID (in UUID form and encrypted) out when someone hits my >> site. >> >> When a user has it, I load up that profile and they "log in" to the site. >> >> If a user doesn't have it, they need to log in with a username and password. >> >> I fail to see why this is insecure. How do you suggest that I >> authenticate that it's the correct person without any user input and >> allowing them to log into the site from more then one computer/ip? >> >> and I'm not falimiar with a spin attack. What is that? >> >> Barney Boisvert wrote: >>> WHAT You store a userId in a cookie and trust it Are you >>> mad??? Numbers are as inherently secure as UUIDs - they're both >>> simply identifiers. Authentication and authorization are where >>> security happens. If an application is susceptible to spin attacks >>> like that, I suppose that a UUID might assist to some degree, but much >>> better to just prevent the spin attack. >>> >>> cheers, >>> barneyb >>> >>> On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector >>> <[EMAIL PROTECTED]> wrote: Oh.. I have that as well. But take for example the UserID that I store as a cookie to someone else based on the UserID field. It's easy to change a cookie to a 1 and hope to get admin access. It's harder to figure out someone elses ID. :) and yeah, I can set it to the IP and so on, but honestly, using a UUID is allot more secure then auto increase. Matt Quackenbush wrote: > On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: > >> The only thing I've noticed in using that is that you can guess the next >> number. >> >> If you have a URL string of id set to 7, I've always tried manually >> typing in 6 and seeing what happens. Sometimes, 5. :) >> >> > That's what permission checking in your application is for. :-) > > > >>> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314485 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
A spin attack is when you manipulate some form of captured user input. It's usually a number, so the name comes from spinning a numeric dial. Any user input, which includes cookies, has to be validated. If you just trust the cookie, anyone who steals the cookie can impersonate the user. Even encrypting it doesn't help, because the attacker doesn't need the actual value, he just has to pass the cookie. On Tue, Oct 28, 2008 at 12:46 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > Perhaps you weren't reading it clearly. Allow me to explain. > > I give the UserID (in UUID form and encrypted) out when someone hits my > site. > > When a user has it, I load up that profile and they "log in" to the site. > > If a user doesn't have it, they need to log in with a username and password. > > I fail to see why this is insecure. How do you suggest that I > authenticate that it's the correct person without any user input and > allowing them to log into the site from more then one computer/ip? > > and I'm not falimiar with a spin attack. What is that? > > Barney Boisvert wrote: >> WHAT You store a userId in a cookie and trust it Are you >> mad??? Numbers are as inherently secure as UUIDs - they're both >> simply identifiers. Authentication and authorization are where >> security happens. If an application is susceptible to spin attacks >> like that, I suppose that a UUID might assist to some degree, but much >> better to just prevent the spin attack. >> >> cheers, >> barneyb >> >> On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector >> <[EMAIL PROTECTED]> wrote: >>> Oh.. I have that as well. But take for example the UserID that I store >>> as a cookie to someone else based on the UserID field. >>> >>> It's easy to change a cookie to a 1 and hope to get admin access. >>> >>> It's harder to figure out someone elses ID. :) >>> >>> and yeah, I can set it to the IP and so on, but honestly, using a UUID >>> is allot more secure then auto increase. >>> >>> Matt Quackenbush wrote: On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: > The only thing I've noticed in using that is that you can guess the next > number. > > If you have a URL string of id set to 7, I've always tried manually > typing in 6 and seeing what happens. Sometimes, 5. :) > > That's what permission checking in your application is for. :-) >>> >> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314478 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Random record identifiers in MySQL 5.0
If you managed to copy a cookie to your machine, then either 1 of 2 things happened. 1) I gave you permission to do so and therefore, I understand the concept that I'm giving you my ID on the site basically. 2) You took it without me knowing. This would involve you accessing my computer in some way and if I don't know you well enough to trust you, then you aren't going to access my machine. Anyway, you can just log into the site from my machine anyway. :) I should also point out that there is a "Logout" function that removes the cookie. So people who are security conscious can log out if needed. Either way, how do you suggest I "authenticate" a person with the cookie to make sure it's really the proper user without having the user re-login to the site? I suppose I can have them relog in if their IP changes, but IP's can be faked as well. Judah McAuley wrote: > What if I copied your cookie to my machine? I go to your site, it > checks to see if I have a cookie, I do, so it grabs the encrypted UUID > value in that cookie, checks it against your db, matches your record, > then logs me in as you. > > I don't have to know the value of the UUID. It doesn't matter that it > is encrypted. I only have to have the same value that you do. > > Judah > > On Tue, Oct 28, 2008 at 12:46 PM, Phillip M. Vector > <[EMAIL PROTECTED]> wrote: >> Perhaps you weren't reading it clearly. Allow me to explain. >> >> I give the UserID (in UUID form and encrypted) out when someone hits my >> site. >> >> When a user has it, I load up that profile and they "log in" to the site. >> >> If a user doesn't have it, they need to log in with a username and password. >> >> I fail to see why this is insecure. How do you suggest that I >> authenticate that it's the correct person without any user input and >> allowing them to log into the site from more then one computer/ip? >> >> and I'm not falimiar with a spin attack. What is that? >> >> Barney Boisvert wrote: >>> WHAT You store a userId in a cookie and trust it Are you >>> mad??? Numbers are as inherently secure as UUIDs - they're both >>> simply identifiers. Authentication and authorization are where >>> security happens. If an application is susceptible to spin attacks >>> like that, I suppose that a UUID might assist to some degree, but much >>> better to just prevent the spin attack. >>> >>> cheers, >>> barneyb >>> >>> On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector >>> <[EMAIL PROTECTED]> wrote: Oh.. I have that as well. But take for example the UserID that I store as a cookie to someone else based on the UserID field. It's easy to change a cookie to a 1 and hope to get admin access. It's harder to figure out someone elses ID. :) and yeah, I can set it to the IP and so on, but honestly, using a UUID is allot more secure then auto increase. Matt Quackenbush wrote: > On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: > >> The only thing I've noticed in using that is that you can guess the next >> number. >> >> If you have a URL string of id set to 7, I've always tried manually >> typing in 6 and seeing what happens. Sometimes, 5. :) >> >> > That's what permission checking in your application is for. :-) > > > >>> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314476 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Random record identifiers in MySQL 5.0
What if I copied your cookie to my machine? I go to your site, it checks to see if I have a cookie, I do, so it grabs the encrypted UUID value in that cookie, checks it against your db, matches your record, then logs me in as you. I don't have to know the value of the UUID. It doesn't matter that it is encrypted. I only have to have the same value that you do. Judah On Tue, Oct 28, 2008 at 12:46 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > Perhaps you weren't reading it clearly. Allow me to explain. > > I give the UserID (in UUID form and encrypted) out when someone hits my > site. > > When a user has it, I load up that profile and they "log in" to the site. > > If a user doesn't have it, they need to log in with a username and password. > > I fail to see why this is insecure. How do you suggest that I > authenticate that it's the correct person without any user input and > allowing them to log into the site from more then one computer/ip? > > and I'm not falimiar with a spin attack. What is that? > > Barney Boisvert wrote: >> WHAT You store a userId in a cookie and trust it Are you >> mad??? Numbers are as inherently secure as UUIDs - they're both >> simply identifiers. Authentication and authorization are where >> security happens. If an application is susceptible to spin attacks >> like that, I suppose that a UUID might assist to some degree, but much >> better to just prevent the spin attack. >> >> cheers, >> barneyb >> >> On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector >> <[EMAIL PROTECTED]> wrote: >>> Oh.. I have that as well. But take for example the UserID that I store >>> as a cookie to someone else based on the UserID field. >>> >>> It's easy to change a cookie to a 1 and hope to get admin access. >>> >>> It's harder to figure out someone elses ID. :) >>> >>> and yeah, I can set it to the IP and so on, but honestly, using a UUID >>> is allot more secure then auto increase. >>> >>> Matt Quackenbush wrote: On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: > The only thing I've noticed in using that is that you can guess the next > number. > > If you have a URL string of id set to 7, I've always tried manually > typing in 6 and seeing what happens. Sometimes, 5. :) > > That's what permission checking in your application is for. :-) >>> >> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314474 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
Perhaps you weren't reading it clearly. Allow me to explain. I give the UserID (in UUID form and encrypted) out when someone hits my site. When a user has it, I load up that profile and they "log in" to the site. If a user doesn't have it, they need to log in with a username and password. I fail to see why this is insecure. How do you suggest that I authenticate that it's the correct person without any user input and allowing them to log into the site from more then one computer/ip? and I'm not falimiar with a spin attack. What is that? Barney Boisvert wrote: > WHAT You store a userId in a cookie and trust it Are you > mad??? Numbers are as inherently secure as UUIDs - they're both > simply identifiers. Authentication and authorization are where > security happens. If an application is susceptible to spin attacks > like that, I suppose that a UUID might assist to some degree, but much > better to just prevent the spin attack. > > cheers, > barneyb > > On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector > <[EMAIL PROTECTED]> wrote: >> Oh.. I have that as well. But take for example the UserID that I store >> as a cookie to someone else based on the UserID field. >> >> It's easy to change a cookie to a 1 and hope to get admin access. >> >> It's harder to figure out someone elses ID. :) >> >> and yeah, I can set it to the IP and so on, but honestly, using a UUID >> is allot more secure then auto increase. >> >> Matt Quackenbush wrote: >>> On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: >>> The only thing I've noticed in using that is that you can guess the next number. If you have a URL string of id set to 7, I've always tried manually typing in 6 and seeing what happens. Sometimes, 5. :) >>> That's what permission checking in your application is for. :-) >>> >>> >>> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314470 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Random record identifiers in MySQL 5.0
-Original Message- From: Judah McAuley <[EMAIL PROTECTED]> Sent: Tuesday, October 28, 2008 2:45 PM To: cf-talk Subject: Re: Random record identifiers in MySQL 5.0 ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314468 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
On Tue, Oct 28, 2008 at 12:37 PM, Jason Fisher <[EMAIL PROTECTED]> wrote:
> @Jim,
>
> If you use CF to generate a UUID, then you do *not* need to hit the DB to
> verify uniqueness. Each call to createUUID() will create a unique value,
> within all limits of reasonableness. So, yes, autoIncrement works as well,
> but to answer your original question, createUUID() is a clean way to create
> unique IDs without undue round-trips to the DB server.
>
For what its worth, I'm usually using CF against an sql server
database and you can get the newly created id right back out when you
do the insert without having to hit the db again
insert (name)
values ('foo bar')
select scope_identity() as new_id
and then adduser.new_id is the value of the newly created autoincremented id.
Judah
~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314466
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
Yeah, I don't think that logically follows at all. An id is not a credential, it shouldn't matter if the id is easy to guess or not. The id is the representation of the item in question. Knowing what/who the item is shouldn't allow you to become that item. I have no problems with using uuid's as primary keys but I don't think it really increases security in any useful fashion. judah On Tue, Oct 28, 2008 at 12:36 PM, Craig Dudley <[EMAIL PROTECTED]> wrote: >> *nods* I do. But the extra layer of UUID is better then using auto increase. > > Personally I strongly disagree with that but hey ho, each to their own ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314464 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Random record identifiers in MySQL 5.0
WHAT You store a userId in a cookie and trust it Are you mad??? Numbers are as inherently secure as UUIDs - they're both simply identifiers. Authentication and authorization are where security happens. If an application is susceptible to spin attacks like that, I suppose that a UUID might assist to some degree, but much better to just prevent the spin attack. cheers, barneyb On Tue, Oct 28, 2008 at 12:30 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > Oh.. I have that as well. But take for example the UserID that I store > as a cookie to someone else based on the UserID field. > > It's easy to change a cookie to a 1 and hope to get admin access. > > It's harder to figure out someone elses ID. :) > > and yeah, I can set it to the IP and so on, but honestly, using a UUID > is allot more secure then auto increase. > > Matt Quackenbush wrote: >> On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: >> >>> The only thing I've noticed in using that is that you can guess the next >>> number. >>> >>> If you have a URL string of id set to 7, I've always tried manually >>> typing in 6 and seeing what happens. Sometimes, 5. :) >>> >>> >> That's what permission checking in your application is for. :-) >> >> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314463 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Random record identifiers in MySQL 5.0
@Jim, If you use CF to generate a UUID, then you do *not* need to hit the DB to verify uniqueness. Each call to createUUID() will create a unique value, within all limits of reasonableness. So, yes, autoIncrement works as well, but to answer your original question, createUUID() is a clean way to create unique IDs without undue round-trips to the DB server. ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314461 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Random record identifiers in MySQL 5.0
> *nods* I do. But the extra layer of UUID is better then using auto increase. Personally I strongly disagree with that but hey ho, each to their own ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314459 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Random record identifiers in MySQL 5.0
*nods* I do. But the extra layer of UUID is better then using auto increase. Barney Boisvert wrote: > And you can guess my login for Gmail from my email address. But in > order to access it, you have to know my credentials. Hopefully you do > similar checks in your applications? > > On Tue, Oct 28, 2008 at 12:13 PM, Phillip M. Vector > <[EMAIL PROTECTED]> wrote: >> The only thing I've noticed in using that is that you can guess the next >> number. >> >> If you have a URL string of id set to 7, I've always tried manually >> typing in 6 and seeing what happens. Sometimes, 5. :) >> >> Now, can you guess another record if the ID is 3219-D87562EFA- etc.? :) >> >> Barney Boisvert wrote: >>> Can't you just use an AUTO_INCREMENT column? That's what they're there for. >>> >>> On Tue, Oct 28, 2008 at 11:40 AM, Jim McAtee <[EMAIL PROTECTED]> wrote: What are you using for random (unique) record identifiers in MySQL? I could use UUIDs, generated either in my CF application, or in MySQL itself. But for a table that's never likely to have more than a few hundred thousand records I could also just use something like a 10 character randomly generated number or string. The advantage of the latter is that it would be a lot easier for any humans that might need to deal with the string. Whatever is used, if the application generates the random identifier, then it needs to first do a record lookup to be sure the identifier is unique, contained within a transaction along with the insert query. Is that avoidable by using the database to generate the identifier? >>> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314453 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Random record identifiers in MySQL 5.0
Oh.. I have that as well. But take for example the UserID that I store as a cookie to someone else based on the UserID field. It's easy to change a cookie to a 1 and hope to get admin access. It's harder to figure out someone elses ID. :) and yeah, I can set it to the IP and so on, but honestly, using a UUID is allot more secure then auto increase. Matt Quackenbush wrote: > On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: > >> The only thing I've noticed in using that is that you can guess the next >> number. >> >> If you have a URL string of id set to 7, I've always tried manually >> typing in 6 and seeing what happens. Sometimes, 5. :) >> >> > That's what permission checking in your application is for. :-) > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314452 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Random record identifiers in MySQL 5.0
And you can guess my login for Gmail from my email address. But in order to access it, you have to know my credentials. Hopefully you do similar checks in your applications? On Tue, Oct 28, 2008 at 12:13 PM, Phillip M. Vector <[EMAIL PROTECTED]> wrote: > The only thing I've noticed in using that is that you can guess the next > number. > > If you have a URL string of id set to 7, I've always tried manually > typing in 6 and seeing what happens. Sometimes, 5. :) > > Now, can you guess another record if the ID is 3219-D87562EFA- etc.? :) > > Barney Boisvert wrote: >> Can't you just use an AUTO_INCREMENT column? That's what they're there for. >> >> On Tue, Oct 28, 2008 at 11:40 AM, Jim McAtee <[EMAIL PROTECTED]> wrote: >>> What are you using for random (unique) record identifiers in MySQL? I >>> could use UUIDs, generated either in my CF application, or in MySQL >>> itself. But for a table that's never likely to have more than a few >>> hundred thousand records I could also just use something like a 10 >>> character randomly generated number or string. The advantage of the >>> latter is that it would be a lot easier for any humans that might need to >>> deal with the string. >>> >>> Whatever is used, if the application generates the random identifier, then >>> it needs to first do a record lookup to be sure the identifier is unique, >>> contained within a transaction along with the insert query. Is that >>> avoidable by using the database to generate the identifier? >>> >>> >>> >> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314451 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Random record identifiers in MySQL 5.0
On Tue, Oct 28, 2008 at 2:13 PM, Phillip M. Vector wrote: > The only thing I've noticed in using that is that you can guess the next > number. > > If you have a URL string of id set to 7, I've always tried manually > typing in 6 and seeing what happens. Sometimes, 5. :) > > That's what permission checking in your application is for. :-) ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314448 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
The only thing I've noticed in using that is that you can guess the next number. If you have a URL string of id set to 7, I've always tried manually typing in 6 and seeing what happens. Sometimes, 5. :) Now, can you guess another record if the ID is 3219-D87562EFA- etc.? :) Barney Boisvert wrote: > Can't you just use an AUTO_INCREMENT column? That's what they're there for. > > On Tue, Oct 28, 2008 at 11:40 AM, Jim McAtee <[EMAIL PROTECTED]> wrote: >> What are you using for random (unique) record identifiers in MySQL? I >> could use UUIDs, generated either in my CF application, or in MySQL >> itself. But for a table that's never likely to have more than a few >> hundred thousand records I could also just use something like a 10 >> character randomly generated number or string. The advantage of the >> latter is that it would be a lot easier for any humans that might need to >> deal with the string. >> >> Whatever is used, if the application generates the random identifier, then >> it needs to first do a record lookup to be sure the identifier is unique, >> contained within a transaction along with the insert query. Is that >> avoidable by using the database to generate the identifier? >> >> >> > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:314445 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random record identifiers in MySQL 5.0
Can't you just use an AUTO_INCREMENT column? That's what they're there for. On Tue, Oct 28, 2008 at 11:40 AM, Jim McAtee <[EMAIL PROTECTED]> wrote: > What are you using for random (unique) record identifiers in MySQL? I > could use UUIDs, generated either in my CF application, or in MySQL > itself. But for a table that's never likely to have more than a few > hundred thousand records I could also just use something like a 10 > character randomly generated number or string. The advantage of the > latter is that it would be a lot easier for any humans that might need to > deal with the string. > > Whatever is used, if the application generates the random identifier, then > it needs to first do a record lookup to be sure the identifier is unique, > contained within a transaction along with the insert query. Is that > avoidable by using the database to generate the identifier? > > > ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:31 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Random Record
One way: HTH J - Original Message - From: "Eric J Hoffman" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Monday, April 08, 2002 5:19 PM Subject: Random Record > What is the best way to get a random record from an table that has > frequent changes and updates...best practices or practical advice from > veterans? Randrange between 1 and recordcount won't work pulling a num > and then going against primary id..so somehow to get a "row"? > > Thanks. > > Regards, > > Eric J. Hoffman > Director of Internet Development > DataStream Connexion, LLC > (formerly Small Dog Design) > > > > __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Random Record
At 11:29 PM 4/8/02 +0200, you wrote: >Eric J Hoffman wrote: > > What is the best way to get a random record from an table that has > > frequent changes and updates...best practices or practical advice from > > veterans? Randrange between 1 and recordcount won't work pulling a num > > and then going against primary id..so somehow to get a "row"? > >Depends on your database. In PostgreSQL I prefer to use: >SELECT * >FROM table >ORDER BY Random() >LIMIT 1 >But in most other databases this doesn't work so I get the entire table >and then use the RandRange() trick to show just 1 column. By the way, the RandRange() trick he was referring to works this way: #queryname.fieldname[randomnumber]# will spit out the record with row number = randomnumber. I think that's the place where Eric was confused about that method. Now available in a San Francisco Bay Area near you! http://www.blivit.org/mr_urc/index.cfm http://www.blivit.org/mr_urc/resume.cfm __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Random Record
This works ok for SQLServer (thanks to Craig Dudley) select top 1 field1, newid() as RandID from tblName order by RandID =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Simon Park Computer Systems Management, Inc. Director, E-Business205 South Whiting Street #201 Ph: 703-823-4300 x119 Alexandria, VA 22304 fax: 703-823-4301 http://www.csmi.com > -Original Message- > From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] > Sent: Monday, April 08, 2002 5:30 PM > To: CF-Talk > Subject: Re: Random Record > > > Eric J Hoffman wrote: > > What is the best way to get a random record from an table that has > > frequent changes and updates...best practices or practical > advice from > > veterans? Randrange between 1 and recordcount won't work > pulling a num > > and then going against primary id..so somehow to get a "row"? > > Depends on your database. In PostgreSQL I prefer to use: > SELECT * > FROM table > ORDER BY Random() > LIMIT 1 > But in most other databases this doesn't work so I get the > entire table > and then use the RandRange() trick to show just 1 column. > > Jochem > > __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: Random Record
Eric J Hoffman wrote: > What is the best way to get a random record from an table that has > frequent changes and updates...best practices or practical advice from > veterans? Randrange between 1 and recordcount won't work pulling a num > and then going against primary id..so somehow to get a "row"? Depends on your database. In PostgreSQL I prefer to use: SELECT * FROM table ORDER BY Random() LIMIT 1 But in most other databases this doesn't work so I get the entire table and then use the RandRange() trick to show just 1 column. Jochem __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: random record
Your method is actually the one that I ended up using, not because I saw that there may be a problem with the other method, but because it seemed a little more straight forward to implement. It seems to be working just fine. JLB -Original Message- From: BORKMAN Lee [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 17, 2001 5:50 PM To: CF-Talk Subject: RE: random record Hi Mark, howzit going? ;-) Actually, I think your method (choosing a random number between 1 and MaxID, then going for the first record >= MaxID) could lead to some very non-random behaviour when you have large gaps in your IDs. The first ID after a large gap will be heavily over-used. I still like my method, sent before. Should actually be random (within limits of RandRange() function). Watcha reckon? Lee (Bjork) Borkman http://bjork.net ColdFusion Tags by Bjork -Original Message- From: Warrick, Mark [mailto:[EMAIL PROTECTED]] . SELECT max (CONTACT_ID) as maxid FROM CONTACTS SELECT MIN (CONTACT_ID) as random_record FROM contacts where CONTACT_ID >= #randnum# #random_record# IMPORTANT NOTICE: This e-mail and any attachment to it is intended only to be read or used by the named addressee. It is confidential and may contain legally privileged information. No confidentiality or privilege is waived or lost by any mistaken transmission to you. If you receive this e-mail in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or use any part of this e-mail if you are not the intended recipient. The RTA is not responsible for any unauthorised alterations to this e-mail or attachment to it. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: random record
Hi Mark, howzit going? ;-) Actually, I think your method (choosing a random number between 1 and MaxID, then going for the first record >= MaxID) could lead to some very non-random behaviour when you have large gaps in your IDs. The first ID after a large gap will be heavily over-used. I still like my method, sent before. Should actually be random (within limits of RandRange() function). Watcha reckon? Lee (Bjork) Borkman http://bjork.net ColdFusion Tags by Bjork -Original Message- From: Warrick, Mark [mailto:[EMAIL PROTECTED]] SELECT max (CONTACT_ID) as maxid FROM CONTACTS SELECT MIN (CONTACT_ID) as random_record FROM contacts where CONTACT_ID >= #randnum# #random_record# IMPORTANT NOTICE: This e-mail and any attachment to it is intended only to be read or used by the named addressee. It is confidential and may contain legally privileged information. No confidentiality or privilege is waived or lost by any mistaken transmission to you. If you receive this e-mail in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or use any part of this e-mail if you are not the intended recipient. The RTA is not responsible for any unauthorised alterations to this e-mail or attachment to it. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: random record
If your using a half-desent Database you can also do: SELECT top 1 * FROM Contacts ORDER BY rand() Some db's such as mysql doesn't like "top 1" but supports "limit 1", just can't remember that syntax right now Bjorn -Original Message- From: Jeremy Bunton [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 17, 2001 2:46 PM To: CF-Talk Subject: RE: random record Thanks Mark that seems to have done the trick. JLB -Original Message- From: Warrick, Mark [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 16, 2001 6:36 PM To: CF-Talk Subject: RE: random record There are many ways to do this. Here's one: SELECT max (CONTACT_ID) as maxid FROM CONTACTS SELECT MIN (CONTACT_ID) as random_record FROM contacts where CONTACT_ID >= #randnum# #random_record# ---mark -- Mark Warrick Phone: (714) 547-5386 Fax: (714) 972-2181 Personal Email: [EMAIL PROTECTED] Personal URL: http://www.warrick.net Business Email: [EMAIL PROTECTED] Business URL: http://www.fusioneers.com ICQ: 346566 -- > -Original Message- > From: Jeremy Bunton [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 16, 2001 1:32 PM > To: CF-Talk > Subject: random record > > > Can anyone point me in the right direction regarding how I would go about > pulling a random record from a table every time a user visits the page. > > JLB > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: random record
Thanks Mark that seems to have done the trick. JLB -Original Message- From: Warrick, Mark [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 16, 2001 6:36 PM To: CF-Talk Subject: RE: random record There are many ways to do this. Here's one: SELECT max (CONTACT_ID) as maxid FROM CONTACTS SELECT MIN (CONTACT_ID) as random_record FROM contacts where CONTACT_ID >= #randnum# #random_record# ---mark -- Mark Warrick Phone: (714) 547-5386 Fax: (714) 972-2181 Personal Email: [EMAIL PROTECTED] Personal URL: http://www.warrick.net Business Email: [EMAIL PROTECTED] Business URL: http://www.fusioneers.com ICQ: 346566 -- > -Original Message- > From: Jeremy Bunton [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 16, 2001 1:32 PM > To: CF-Talk > Subject: random record > > > Can anyone point me in the right direction regarding how I would go about > pulling a random record from a table every time a user visits the page. > > JLB > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: random record
There are many ways to do this. Here's one: SELECT max (CONTACT_ID) as maxid FROM CONTACTS SELECT MIN (CONTACT_ID) as random_record FROM contacts where CONTACT_ID >= #randnum# #random_record# ---mark -- Mark Warrick Phone: (714) 547-5386 Fax: (714) 972-2181 Personal Email: [EMAIL PROTECTED] Personal URL: http://www.warrick.net Business Email: [EMAIL PROTECTED] Business URL: http://www.fusioneers.com ICQ: 346566 -- > -Original Message- > From: Jeremy Bunton [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 16, 2001 1:32 PM > To: CF-Talk > Subject: random record > > > Can anyone point me in the right direction regarding how I would go about > pulling a random record from a table every time a user visits the page. > > JLB > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: random record
At 04:31 PM 1/16/01 -0500, you wrote: >Can anyone point me in the right direction regarding how I would go about >pulling a random record from a table every time a user visits the page. query the table as normal use RandRange(1,#query.RecordCount#) to select a random number Output only that record like so. #query.field1[randomnumber]# ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: random record
You could choose a random number from [1] to [recordcount], and use a counter variable to get to that record. Or, if you have an identity key that's a autonumber you could use that... > -Original Message- > From: Jeremy Bunton [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, January 16, 2001 4:32 PM > To: CF-Talk > Subject: random record > > > Can anyone point me in the right direction regarding how I would go about > pulling a random record from a table every time a user visits the page. > > JLB > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: random record
Hi Jeremy, there are many ways, but this is the easiest I have found: SELECT * FROM testTABLE #data# Cache the query for better performance. Best of luck, Lee (Bjork) Borkman http://bjork.net ColdFusion Tags by Bjork -Original Message- From: Jeremy Bunton [mailto:[EMAIL PROTECTED]] Can anyone point me in the right direction regarding how I would go about pulling a random record from a table every time a user visits the page. IMPORTANT NOTICE: This e-mail and any attachment to it is intended only to be read or used by the named addressee. It is confidential and may contain legally privileged information. No confidentiality or privilege is waived or lost by any mistaken transmission to you. If you receive this e-mail in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or use any part of this e-mail if you are not the intended recipient. The RTA is not responsible for any unauthorised alterations to this e-mail or attachment to it. ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: random record
Here a couple scripts. Pull a random record. http://cfhub.com/discussion/viewmessages.cfm?Forum=5&Topic=170 Pull a list of random records: http://cfhub.com/discussion/viewmessages.cfm?Forum=8&Topic=178 > Can anyone point me in the right direction regarding how I would go about > pulling a random record from a table every time a user visits the page. > > JLB > > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Random Record
This approach won't work if the RecordIDs are not sequential. You'll end up with blank records sometimes. -- Mark Warrick Phone: (714) 547-5386 Efax.com Fax: (801) 730-7289 Personal Email: [EMAIL PROTECTED] Personal URL: http://www.warrick.net Business Email: [EMAIL PROTECTED] Business URL: http://www.fusioneers.com ICQ: 346566 -- > -Original Message- > From: Brian bouldernet [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 05, 2000 7:00 PM > To: CF-Talk > Subject: RE: Random Record > > > x = total available records > > > SELECT * FROM TABLE WHERE ID = Random Count > > > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 05, 2000 4:49 PM > To: CF-Talk > Subject: Random Record > > > How could i select a random record from a table? > > Like to set my query as > > > SELECT * > FROM Table > WHERE RecordID = ?? > > > Anyone know how to do this? > > thanks > > kev > -- > -- > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf _talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Random Record
Here's one way (not necessarily the best way). SELECT * FROM Table WHERE RecordID = ?? Note that if you were to simply generate a record ID based upon the total records, you probably won't get a record if the ID numbers are not sequential. (As is usually the case.) ---mark -- Mark Warrick Phone: (714) 547-5386 Efax.com Fax: (801) 730-7289 Personal Email: [EMAIL PROTECTED] Personal URL: http://www.warrick.net Business Email: [EMAIL PROTECTED] Business URL: http://www.fusioneers.com ICQ: 346566 -- > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Thursday, October 05, 2000 4:49 PM > To: CF-Talk > Subject: Random Record > > > How could i select a random record from a table? > > Like to set my query as > > > SELECT * > FROM Table > WHERE RecordID = ?? > > > Anyone know how to do this? > > thanks > > kev > -- > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf > _talk or send a message to [EMAIL PROTECTED] with > 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Random Record
yep... You're rigth pan. I will change my programs... thanks! ~Juandres - Original Message - From: pan <[EMAIL PROTECTED]> To: CF-Talk <[EMAIL PROTECTED]> Sent: Friday, October 06, 2000 2:04 PM Subject: Re: Random Record > From: "Juan Andres Alvarez Valenzuela" <[EMAIL PROTECTED]> > > > > I use the database for that (better? I think ): > > > > SELECT count(*) total, > > (ceiling((RAND( (DATEPART(ms, GETDATE()) * 10 )+ (DATEPART(ss, > > GETDATE()) * 1000 )+ DATEPART(mm, GETDATE()) ))*count(*))) rand > > FROM YourTable > > > > It gives you one record, something like: > > > > totalRand > > - > > 1100120.0 > > > > record selected: 120 > > > That's a good method for generating a random number in the range of > the count of records in a table, but how does this provide any pointer > to a specific row in that table? > It works if you subsequently query all records of YourTable and then > references query[120], but the overhead is higher in both memory and > processing time. Also, count(*) will catch nulls which will (if there are > any) invalidate the range for selecting a random number. > The second query after generating a random should be as quick and > efficient as possible - selecting all data from a table and then > referencing query[random] can take more time and memory than > a second query that selects one specific row. > > Also, rand() generates an error for MS-Access - need to use rnd(), but > there is no way to seed rnd() in a sql statement for Access. The random > number needs to be built outside the query. > > > Pan > > > > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Random Record
If that's the case just do this: SELECT * FROM QUOTES Random Record No = #selectrandomrecord.ID# Quote = #selectrandomrecord.Quote# Author = #selectrandomrecord.Author# Larry Juncker Senior Cold Fusion Programmer Heartland Communications Group, Inc. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 05, 2000 10:52 PM To: CF-Talk Subject: Re: Random Record well what i want to be able to do is like show a random quote from a table of quotes Now some may be deleted, some may be added, in which the would not work because the "ID" numbers in the table would be changing, is there a way to specify like a certain Row number in my SQL WHERE statement? like... SELECT * FROM Quotes WHERE ***TableRow = Random Row*** can that type of function be completed??? kev -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Random Record
What about this: SELECT MAX(RecordID) AS MaxID FROM TABLE SELECT * FROM Table WHERE RecordID = #RandNum# Larry Juncker Senior Cold Fusion Programmer Heartland Communications Group, Inc. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 05, 2000 6:49 PM To: CF-Talk Subject: Random Record How could i select a random record from a table? Like to set my query as SELECT * FROM Table WHERE RecordID = ?? Anyone know how to do this? thanks kev -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Random Record
From: "Juan Andres Alvarez Valenzuela" <[EMAIL PROTECTED]> > I use the database for that (better? I think ): > > SELECT count(*) total, > (ceiling((RAND( (DATEPART(ms, GETDATE()) * 10 )+ (DATEPART(ss, > GETDATE()) * 1000 )+ DATEPART(mm, GETDATE()) ))*count(*))) rand > FROM YourTable > > It gives you one record, something like: > > totalRand > - > 1100120.0 > > record selected: 120 That's a good method for generating a random number in the range of the count of records in a table, but how does this provide any pointer to a specific row in that table? It works if you subsequently query all records of YourTable and then references query[120], but the overhead is higher in both memory and processing time. Also, count(*) will catch nulls which will (if there are any) invalidate the range for selecting a random number. The second query after generating a random should be as quick and efficient as possible - selecting all data from a table and then referencing query[random] can take more time and memory than a second query that selects one specific row. Also, rand() generates an error for MS-Access - need to use rnd(), but there is no way to seed rnd() in a sql statement for Access. The random number needs to be built outside the query. Pan -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Random Record
I use the database for that (better? I think ): SELECT count(*) total, (ceiling((RAND( (DATEPART(ms, GETDATE()) * 10 )+ (DATEPART(ss, GETDATE()) * 1000 )+ DATEPART(mm, GETDATE()) ))*count(*))) rand FROM YourTable It gives you one record, something like: totalRand - 1100120.0 record selected: 120 ~Juandres - Original Message - From: Lee Borkman <[EMAIL PROTECTED]> To: CF-Talk <[EMAIL PROTECTED]> Sent: Friday, October 06, 2000 7:24 PM Subject: RE: Random Record Guys, this will only work if the IDs start at 1, and there are no missing IDs. Also, the #s are unnecessary in your CFSET. Have fun, Lee. Bjork.Net [EMAIL PROTECTED] (Brian bouldernet) wrote: x = total available records SELECT * FROM TABLE WHERE ID = Random Count -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 05, 2000 4:49 PM To: CF-Talk Subject: Random Record How could i select a random record from a table? Like to set my query as SELECT * FROM Table WHERE RecordID = ?? Anyone know how to do this? thanks kev -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. Get free email and a permanent address at http://www.netaddress.com/?N=1 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=sts&body=sts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Random Record: methods
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > well what i want to be able to do is like show a random quote from a table > of quotes > > Now some may be deleted, some may be added, in which the > > > > would not work because the "ID" numbers in the table would be changing, is > there a way to specify like a certain Row number in my SQL WHERE statement? > Transfer the discontinuous list of id numbers to a continuous one. METHOD I METHOD II select QUOTE from quoteTable where ID='#getID.ID[randomID]#'> Often an intractable problem involving a domain of elements that aren't suspectible to simple analyses is resolvable by abstracting one, or more, level(s) away from the domain. Works for lots of problems. The first example shows an explicit transfer of seemingly disorganized data to a nice monotonically increasing ordered domain of id numbers that starts with 1 and ends with the count of all possible id numbers that we select randomly from in order to point at a record in the quotes db. The second example implicitly uses the inherent simple ordering of a query for the same purpose. In both examples we end up not caring what gets done to the list of id numbers in the quotes db - we can still accomplish our goal. As a further note: if editing of the quoteTable is done in a way that always leaves only returnable items in the table then Method II will always work and the benefit of not using an array accrues. If editing of the quoteTable is done such that some quotes are marked as temporarily non-returnable, then Method I would work better as the array can be built from a query that gets both ID and a field used to mark a quote as currently useable or un-useable. Thus, in the cfloop that assigns ID numbers to the array we can test for the value of the second field and make the assignment or not as indicated. METHOD I - extended [assume quotes.quoteTable.useThisQuote is boolean or is some other two-valued field] As a final further note: what I would actually do in this kind of case is use a stored query in the quotes db that lets the db do the work of most of Methods I and II. Method III (Build a query in db that creates a list comprised of all IDs that are currently useable or otherwise selected.) then, use Method II querying the stored query from the quotes table and so on ... This allows for the easy use of several stored queries that are built according to different purposes - such as randomly selecting a quote from a disorganized group of ids, selecting from a group of ids currently marked as useable, selecting from a group of ids related to a specific topic or user or date, etc. Pan 'currently available for hire' -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: [Re: Random Record]
Kev, Don't look right past my previous answer. It works like a charm. It doesn't care what your IDs are called, or how they are sequenced. For best results, cache the query. As I said before, there's no way to do this is SQL alone. So do this: SELECT * FROM quotes #QuoteText# TRY IT. YOU'LL LIKE IT!!! Lee. -- [EMAIL PROTECTED] wrote: well what i want to be able to do is like show a random quote from a table of quotes Now some may be deleted, some may be added, in which the would not work because the "ID" numbers in the table would be changing, is there a way to specify like a certain Row number in my SQL WHERE statement? like... SELECT * FROM Quotes WHERE ***TableRow = Random Row*** can that type of function be completed??? kev -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. Get free email and a permanent address at http://www.netaddress.com/?N=1 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Random Record
get id from table Show Quote Try again -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 05, 2000 8:52 PM To: CF-Talk Subject: Re: Random Record well what i want to be able to do is like show a random quote from a table of quotes Now some may be deleted, some may be added, in which the would not work because the "ID" numbers in the table would be changing, is there a way to specify like a certain Row number in my SQL WHERE statement? like... SELECT * FROM Quotes WHERE ***TableRow = Random Row*** can that type of function be completed??? kev -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Random Record
well what i want to be able to do is like show a random quote from a table of quotes Now some may be deleted, some may be added, in which the would not work because the "ID" numbers in the table would be changing, is there a way to specify like a certain Row number in my SQL WHERE statement? like... SELECT * FROM Quotes WHERE ***TableRow = Random Row*** can that type of function be completed??? kev -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Random Record
In the event you do not wish to select all the full row for all records, you could just grab all the keys in the first query and come around again for the full row: selectkey fromtable select* fromtable wherekey = '#randomKey#' Which would be infinitely faster if you're dealing with large text fields or something. Have fun! ;-) - Original Message - From: "Jason Stiefel" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, October 05, 2000 10:13 PM Subject: Re: Random Record > kev- > > > selectmyfields > frommytable > > > > > > > #myQuery[intRecord].field# > > You can treat any query as if it were an array. > > - Original Message - > From: <[EMAIL PROTECTED]> > To: "CF-Talk" <[EMAIL PROTECTED]> > Sent: Thursday, October 05, 2000 7:49 PM > Subject: Random Record > > > > How could i select a random record from a table? > > > > Like to set my query as > > > > > > SELECT * > > FROM Table > > WHERE RecordID = ?? > > > > > > Anyone know how to do this? > > > > thanks > > > > kev > > -- > > > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > > To Unsubscribe visit > http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or > send a message to [EMAIL PROTECTED] with 'unsubscribe' in > the body. > > > > > > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. > > -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Random Record
Guys, this will only work if the IDs start at 1, and there are no missing IDs. Also, the #s are unnecessary in your CFSET. Have fun, Lee. Bjork.Net [EMAIL PROTECTED] (Brian bouldernet) wrote: x = total available records SELECT * FROM TABLE WHERE ID = Random Count -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 05, 2000 4:49 PM To: CF-Talk Subject: Random Record How could i select a random record from a table? Like to set my query as SELECT * FROM Table WHERE RecordID = ?? Anyone know how to do this? thanks kev -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. Get free email and a permanent address at http://www.netaddress.com/?N=1 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Random Record
kev- selectmyfields frommytable #myQuery[intRecord].field# You can treat any query as if it were an array. - Original Message - From: <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Thursday, October 05, 2000 7:49 PM Subject: Random Record > How could i select a random record from a table? > > Like to set my query as > > > SELECT * > FROM Table > WHERE RecordID = ?? > > > Anyone know how to do this? > > thanks > > kev > -- > Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ > To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. > > -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: Random Record
x = total available records SELECT * FROM TABLE WHERE ID = Random Count -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 05, 2000 4:49 PM To: CF-Talk Subject: Random Record How could i select a random record from a table? Like to set my query as SELECT * FROM Table WHERE RecordID = ?? Anyone know how to do this? thanks kev -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
Re: Random Record
Hi Kev, As far as I know, there is no universal way to do this in SQL. In CF you can do this: SELECT * FROM Staff #FirstName# Hope that helps, Lee Bjork Borkman Bjork.Net - ColdFusion Tags by Bjork [EMAIL PROTECTED] wrote: How could i select a random record from a table? Like to set my query as SELECT * FROM Table WHERE RecordID = ?? Anyone know how to do this? thanks kev -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body. Get free email and a permanent address at http://www.netaddress.com/?N=1 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebarRsts&bodyRsts/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
RE: random record query retrieval
> I have a database of 4,000 or so records. I would like to do a query based > on some conditions (i.e. where active =1 AND group_id = 45). I > can do this. > The problem I have having is that out of the conditional query I > would like > to pull a random 3 records out and have use and be able to output the data > in those random 3 rows. So, to recap...4,000 records in a database. I > might have a query where 500 out of the 4,000 match my > conditional criteria. > Out of that 500 that gets returned in the query I would like to select a > random 3 out of only that 500 returned and have use of the data.. > Can anyone help? OK, one simple solution to this would be to run your criteria, and only return the Identity, then select 3 random entries from this (with a little logic to ensure you don't have the same record twice), then get these 3 records from the main database; select ID from myTable where myField='#myValue#' and myField2=#myValue2# select * from myTable where ID in (#myIDs#) Problems will arise if your results are less than 3 records (infinite loops), but you can add the logic for that. HTH Philip Arnold ASP Multimedia Limited T: +44 (0)20 8680 1133 "Websites for the real world" ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. ** -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
