SQL Query

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
Monty
Forum Moderator
Posts: 10000
Joined: 05 Feb 2004, 02:00
Processor: Intel i5-4690K @ 4.5GHZ
Motherboard: ASUS Maximus VII Formula
Graphics card: ASUS GTX970 Strix
Memory: 4 x 4GB Corsair Dominators
Location: Messing with your Mind
Contact:

SQL Query

Post by Monty »

If someone wanted to have a very simple WAP site and wanted to restrict access to a certain group (based on a forum usergroup) -would they be able to do it with a simple username and password query box and compare it to the forum database?


I guess the code would be something like this (note, this is not proper code):

Code: Select all

Pseudo Code

username =  x;
Password = y;
validate x & y;
check username permisions; //Checks username against usergroup names
return true - allow access;
return false - authorized users only;
Art Williams wrote:I'm not telling you it is going to be easy, I'm telling you it's going to be worth it.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query

Post by RuadRauFlessa »

Monty wrote:If someone wanted to have a very simple WAP site and wanted to restrict access to a certain group (based on a forum usergroup) -would they be able to do it with a simple username and password query box and compare it to the forum database?


I guess the code would be something like this (note, this is not proper code):

Code: Select all

Pseudo Code

username =  x;
Password = y;
validate x & y;
check username permisions; //Checks username against usergroup names
return true - allow access;
return false - authorized users only;
Yes if you make use of stored procedures which does the checking. And then pass the username to the stored proc with all the other parameters.
Then you can actually make the access transparent from the codes side.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Monty
Forum Moderator
Posts: 10000
Joined: 05 Feb 2004, 02:00
Processor: Intel i5-4690K @ 4.5GHZ
Motherboard: ASUS Maximus VII Formula
Graphics card: ASUS GTX970 Strix
Memory: 4 x 4GB Corsair Dominators
Location: Messing with your Mind
Contact:

Re: SQL Query

Post by Monty »

Thanks Raud!
Art Williams wrote:I'm not telling you it is going to be easy, I'm telling you it's going to be worth it.
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Query

Post by rustypup »

never... never... send credentials in plain...

use the provided username and password to produce a comparable fingerprint, (even a simple MD5 hash).

secondly, don't confirm access via a boolean or bit switch... use an opaque code, or deliver a failed page...
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Monty
Forum Moderator
Posts: 10000
Joined: 05 Feb 2004, 02:00
Processor: Intel i5-4690K @ 4.5GHZ
Motherboard: ASUS Maximus VII Formula
Graphics card: ASUS GTX970 Strix
Memory: 4 x 4GB Corsair Dominators
Location: Messing with your Mind
Contact:

Re: SQL Query

Post by Monty »

rustypup wrote:never... never... send credentials in plain...

use the provided username and password to produce a comparable fingerprint, (even a simple MD5 hash).

secondly, don't confirm access via a boolean or bit switch... use an opaque code, or deliver a failed page...
I kind of understood Raud's post (with much mental straining and lots of slow reading), but Rusty, you just shot way over my head :oops: (well, mainly the bold bit)
Art Williams wrote:I'm not telling you it is going to be easy, I'm telling you it's going to be worth it.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query

Post by RuadRauFlessa »

rustypup wrote:never... never... send credentials in plain...

use the provided username and password to produce a comparable fingerprint, (even a simple MD5 hash).

secondly, don't confirm access via a boolean or bit switch... use an opaque code, or deliver a failed page...
Passing the username does not mean it is in clear text. TBH it is more like passing a search parameter to a stored procedure on a SQL server over an encrypted tunnel to port 1433 on a SQL server. Nothing llike passing credentials. The user already has to be logged in. The only thing done is that the stored proc uses the, already authenticated, user's name to internally check access rights to specific data and then return what is relevant to the user's rights rather than returning everything.

psudo code..... customer search...... whatever...

Code: Select all

create stored proc with parameters userid, customernamewildcard
begin
  create a table variable in which to temporarily store data in the memory of the server rather than using a temp table which will cause extra disk IO
  create a cursor which will loop through the customers which is returned when searching for the customernamewildcard in the customer's name field
  for each customer 
  begin
    if user is allowed access in the customer's region
    begin
      add the customer to the table variable
    end
  end
  return the contents of the table variable
end
Doing it this way ensures that the access rights is abstracted from the actual front end code. It is however still a bundle of extra work for you and the server which has to run the database. So unless you have plenty of time and you have a killer of a server it is generally not a good idea to go and do this.

But lets consider what the above will do....
If the user has rights in Gauteng and he runs a customer search the stored proc will only return what the user has rights to see. If the customer he is searching for is located in Limpopo then the customer won't be returned by the stored proc and thus the user won't see the customer.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Query

Post by rustypup »

opaque as in non-transparent... it's as easy to spoof standard success codes, (in standard HTTP/FTP traffic these are all in the 2XX range..), as it is to spot a boolean or bit flag...

better yet, serve up the relevant page based on a short-lived url mask, which goes some way toward foiling this type of spoofing... that way, whether login succeeds or fails, the user is exposed to something as benign and informative as hKJKnkjjhg2HJHJKjkhkh8KLJHJK

process would be:
  • :-post credentials fingerprint to server
    :-server confirms credentials fingerprint
    :-server creates temporary URL mask pointing to HOME, if credentials cleared or FAILED if not
    :-server responds with temporary URL
@Ruad: serving a WAP site means it's going to be clear text at some point, hence my hesitation..
Most people would sooner die than think; in fact, they do so - Bertrand Russel
Monty
Forum Moderator
Posts: 10000
Joined: 05 Feb 2004, 02:00
Processor: Intel i5-4690K @ 4.5GHZ
Motherboard: ASUS Maximus VII Formula
Graphics card: ASUS GTX970 Strix
Memory: 4 x 4GB Corsair Dominators
Location: Messing with your Mind
Contact:

Re: SQL Query

Post by Monty »

I've just found out more information - the guys I was looking into this for aren't going to implement a system like this as not all the users of the wap site will be on the forum
Art Williams wrote:I'm not telling you it is going to be easy, I'm telling you it's going to be worth it.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query

Post by RuadRauFlessa »

rustypup wrote:@Ruad: serving a WAP site means it's going to be clear text at some point, hence my hesitation..
+1

But I was under the impression we are talking about back end as he was asking about queries.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Monty
Forum Moderator
Posts: 10000
Joined: 05 Feb 2004, 02:00
Processor: Intel i5-4690K @ 4.5GHZ
Motherboard: ASUS Maximus VII Formula
Graphics card: ASUS GTX970 Strix
Memory: 4 x 4GB Corsair Dominators
Location: Messing with your Mind
Contact:

Re: SQL Query

Post by Monty »

RuadRauFlessa wrote:
rustypup wrote:@Ruad: serving a WAP site means it's going to be clear text at some point, hence my hesitation..
+1

But I was under the impression we are talking about back end as he was asking about queries.
My heading may be misleading - I meant query as in question :oops:
Art Williams wrote:I'm not telling you it is going to be easy, I'm telling you it's going to be worth it.
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query

Post by RuadRauFlessa »

Monty wrote:
RuadRauFlessa wrote:
rustypup wrote:@Ruad: serving a WAP site means it's going to be clear text at some point, hence my hesitation..
+1

But I was under the impression we are talking about back end as he was asking about queries.
My heading may be misleading - I meant query as in question :oops:
Hehe hence me answering it from a SQL perspective.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Post Reply