Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

accessing user table.

Quote Reply
accessing user table.
How would I go about accessing the user table from a php script?

What I am trying to do is use the same "Username" and "Password" found in the User table, and use it with another script.



[This message has been edited by Robert_B (edited April 20, 2000).]
Quote Reply
Re: accessing user table. In reply to
<?php

function connect(){
$link = @mysql_pconnect ("localhost", "******", "******");
if ($link && mysql_select_db("michaelb"));
return ($link);
return (FALSE);
}

connect();

$query = "select username,password from usertable where something='something'";

$result = mysql_query($query);

$row = mysql_fetch_array($result);

$user = $row[0];
$pass = $row[1];

?>

Hope that helps - Its just the code I would use to grab the user/pass using PHP.

------------------
Michael Bray
Review your webhost or find a new one.
www.webhostarea.com
Links SQL User
------------------




Quote Reply
Re: accessing user table. In reply to
The mySQL user table is called "Users", correct?

------------------
Robert Blackstone
Webmaster of Scato Search
www.scato.com


[This message has been edited by Robert_B (edited April 20, 2000).]
Quote Reply
Re: accessing user table. In reply to
First, don't confuse the mysql.user table with the Links.Users table!

User information for Links is stored in the Users table.

The Links table has the following structure:
Code:

Username char(25) NOT NULL,
Password char(25) binary NOT NULL,
Email char(50),
Validation char(20),
Status enum('Not Validated','Registered','Editor','Administrator') DEFAULT 'Not Validated' NOT NULL,
PRIMARY KEY (Username),
KEY sndx (Status)



Your's may vary slightly depending on if you've made any mods.




Quote Reply
Re: accessing user table. In reply to
So which of these lines would be correct, for I am using the Links Users table:

Code:
SELECT * FROM Links.Users WHERE username = '$username'
or
Code:
SELECT * FROM Users WHERE username = '$username'

------------------
Robert Blackstone
Webmaster of Scato Search
www.scato.com
Quote Reply
Re: accessing user table. In reply to
They would be the same, since Users is a table in the Links database, it's referred to by it's full name: Links.Users, or by it's relative name: Links, if you've selected a database to use (as with $dbh-> )

Links.Users will work inside any script, "Users" will work inside any Links script that is operating on the Links database.

If you had a wwwthreads database, with a Users table in it, you'd probably want to use the full names at all times to prevent any confusion:

wwwthreads.Users and Links.Users

Make sense?

MySQL allows you to use many different databases, as long as your user has permissions in those databases, and you specify which database.table you want.


Quote Reply
Re: accessing user table. In reply to
Actually neither would work as you didn't capitilize the u in username. Wink

Remember, if you are on a unix system, things are case sensitive. So you want:

SELECT * FROM Users WHERE Username = '$username'

Now I don't know if PHP will auto quote things or not, but be sure to check and quote your input!

Suppose you do:

$query = "SELECT * FROM Users WHERE Username = '$username' AND Password = '$password'"
$result = mysql_query ($query);
$row = mysql_fetch_array ();
if ($row) {
.. person is logged in
}
else{
.. person isn't logged in.
}

Now what happens if I enter in nothing for username and for password I enter:

' OR '1

what you will get for a query is:

SELECT * FROM Users WHERE Username = '$username' AND Password = '' OR 1

which will return all users and log me in! Ack!

Lesson for the day: always quote/escape any user input.

Cheers,

Alex
Quote Reply
Re: accessing user table. In reply to
Picky, Picky, Picky ! Smile

A problem I have is the (code) tags turn the text into about 5 pixel high writing in MSIE, so I have to guess -- or change settings. Easy to miss things like that <G>

What Alex is saying about the quotes is very important!

You want to wrap the WHOLE SELECT statement in a set of double-" "and put each "value" based term into a set of single-' '.

You don't want to use ' ' around $value parameters that YOU SET in the script, only around things that are to be INTERPRETED or are passed by a user.

Ex:

$value = ' AND $column_name = 1';

You don't want to put '' around the $value, since you want that interpreted as a part of the overall SELECT statement. _BUT_ you need to make sure what you stuff in $column_name is _NOT_ user input, but something you PICK FROM A LIST based on the user input! (NO UNSAFE PROGRAMMING!)

On the other hand:

$value = $in->param('some_value');

you'd want to turn into '$value' after you did some checks on the data, so that when you used it:

$query = qq| " SELECT * FROM $table
$where
AND $column_name='$value' "|;

what will happen is the "" around the whole statement will pass the ' as strings, and not as special characters.

I think I got this right! -- Don't forget the " around the whole statement, as well as the ' around the _values_.


This still brings up a problem..... even with autoescape, and escaping, shouldn't you test the data to make sure it contains ONLY characters, numbers and underscore characters any way??

In search -- the only script that a value is passed in from the user and acted upon, the input is digested 10 different ways before it's used.

In the login script -- a user could potentially pass anything in as a username or password.

Anything you do should be preceeded by a test for anything out of the ordinary. Never trust any values passed by a user. If you are expecting one of 10 words, check for those 10 words specifically. If you are expecting only alpha-numerics check for a-zA-Z0-9_

The SQL engine adds another layer of complexity and potential security holes if an un-parsed un-escaped query can be passed, a hacker could get into your data base -- or sensitive data out of it.







Quote Reply
Re: accessing user table. In reply to
:P - Heres a quick fix for that one Alex.

if (empty($username)){
$error = "Error Empty field";
DIE("$error");
}

------------------
Michael Bray
Review your webhost or find a new one.
www.webhostarea.com
Links SQL User
------------------




Quote Reply
Re: accessing user table. In reply to
While that may ensure the field is not empty, that still doesn't protect you from malicious input.


Quote Reply
Re: accessing user table. In reply to
I know,

But it stops alex's scenario of them leaving the username blank and entering ' OR '1 as the password.



------------------
Michael Bray
Review your webhost or find a new one.
www.webhostarea.com
Links SQL User
------------------




Quote Reply
Re: accessing user table. In reply to
Anytime you are going to use something passed from a user as part of a command, or worse -- as a pass to the system shell -- you need to verify it, clean it, escape it, and do it all again!

You need to check that email addresses are really email addresses. You can pass sendmail a string that opens your server up to hacker attacks.

Passing a username or password to a program without authenticating it first is a bad idea.

For instance, let's say someone is trying to hack your root password. If you do a look up on the ID before you attempt to use it, then you block this sort of hacker.

For instance:

A person is supposed to pass in a userID and Password. They pass in some strings.

First, validate the userID is actually only letters and numbers, then do a

"SELECT FROM Users where UserID=$userid"

If you don't get any hits, don't even bother checking the password ... just return an invalid log-on message. Don't tell them the UserID didn't match if you don't want to. Depends on what sort of security you want. If you want the option of sending UserID's/passwords to people who forget them, then you might as well tell them if the userID exists -- unless you tell _EVERYONE_ who tries that their password is being sent to the registered address (not that the UserID wasn't found).

If you give someone the fact that the Username exists, but the password was wrong, they can just keep trying to crack it. If you don't tell them anything, then they don't know if anything they have is correct.

It's the "MasterMind" concept -- remember that game?

As sites do more and more, and more information about users are stored on-line -- even address or email -- privacy and security is a big thing. Most users have easy to crack passwords.







------------------
POSTCARDS.COM -- Everything Postcards on the Internet www.postcards.com
LinkSQL FAQ: www.postcards.com/FAQ/LinkSQL/








Quote Reply
Re: accessing user table. In reply to
Using the select command on the username is a good idea... 3 Lines of code to get the number of rows then an if statement... I'll add that to something I'm doing now...

Mastermind was a cool game Smile

------------------
Michael Bray
Review your webhost or find a new one.
www.webhostarea.com
Links SQL User
------------------




Quote Reply
Re: accessing user table. In reply to
You still need to quote all user input!

Now, you can't run multiple queries in mysql now, but you could possible do it in the future.

So what if I entered in my username as:

'; DELETE FROM Users;

and the select would fail, and then your Users table would be wiped out. (Don't worry, you can't do this in mysql, however it is possible in other databases, and could easily be a feature added to mysql later).

I would at a minimum escape all single quotes.

Cheers,

Alex
Quote Reply
Re: accessing user table. In reply to
I'm _not_ minimizing the escape and clean features!

But, if you know already that your input cannot contain anything except alpha characters and numbers, look for them, if anything else is found, bounce it.

Anything passed to a query needs to be cleaned. FWIW I can't see any reason to pass a semi colon, comma or quotes to the logon routine. The user might type them in, but they should be taken out before the string is acted on. There are great routines for validating email addresses -- including ones that query the mail host to see if it will accept the mail.

A single quote mark needs to be handled for contractions "don't" for example.

Originally, I wrote a reply discussing the quote features/logic but then realized it might be accurate, so I killed it. I may try my hand at it again .... the idea of "" a select, and '' a term, and such.

Alex confirmed in another thread that you can escape/quote input by doing:

$quoted = $DBH->quote('something');

the $quoted string will come back quoted, and escaped, for insertion into a MySQL database, and probably for the queries as well. If you put '$quoted' you'll create some problems, so you don't want to do that.

This is a pass-through to the DBI module 'escape' function. You can read about it in the docs.

Quote Reply
Re: accessing user table. In reply to
Michael_Bray how did you get displayreview.php to work with UBB, i would like to know.

Thanx
Quote Reply
Re: accessing user table. In reply to
I just made a review script in PHP for Links SQL - and added the features of the UBB cause I thought it'd be cool Smile

Check out - http://216.55.12.182/forum/index.php - New forum for my site on the dedicated server Smile

Its all PHP and MySQL.

With the Zend Optimiser, and the soon to be released Zend Cache -> I feel this is the best way to go.

BTW - For the user database thingo above, add this line in if you are using PHP

Code:
$text=str_replace("'","",$text);

Just a random PHP tip I thought I'd throw in :P

------------------
Michael Bray
Review your webhost or find a new one.
www.webhostarea.com
Links SQL User
------------------




Quote Reply
Re: accessing user table. In reply to
Michael,

Nice forum. Does it tie into user.cgi, or is it independent of Links SQL?

Also who is hosting your dedicated server?

------------------
Robert Blackstone
Webmaster of Scato Search
www.scato.com


[This message has been edited by Robert_B (edited April 26, 2000).]
Quote Reply
Re: accessing user table. In reply to
Virtualis is hosting it,

I'm sharing it with someone else from Australia though....

Right now, without any hacking done to it it is independant of Links SQL, but when I finish everything there it will all be using the same user database.

I don't see the point in running 2/3 different ones.


------------------
Michael Bray
Review your webhost or find a new one.
www.webhostarea.com
Links SQL User
------------------