Dreamweaver/Website Database

[iGoD ReleNtLeS]

Ok, im in the process of creating my own website. I've done all the designing, layout and so on, but now its getting to the stuff i vaguely understand.

Im wanting to create a login system where members can access a members only area and store a display picture and several details about themselves, much like the one on the novatech forum.

Now i know there needs to be a database of some sort and that there are several extensions for this sort of thing. Has anyone got any recommendations for this? I would be willing to pay a reasonable price for it, say upto £10, but nothing stupid like £50, £60 as im only going to be using it for this one website.

Now how do i go about this database, do i create it using MS Access or code it in notepad or something?

Any help would be greatly appreciated, Thanks

[Ian.H]

MySQL: Free
PHP: Free
Zend Framework: Free


I use phpMyAdmin (http://www.phpmyadmin.net/) for administering MySQL databases as it provides an easy to use web interface. The Zend Framework (assuming you're using PHP) has an easy to use database class.

That said, you _will_ want to make sure that it's secure, as 95% of website compromises these days come via SQL injections due to poorly constructed SQL statements.



Regards,

Ian

[Rhys]

Its not so much poorly structured sql statements but more the input into them.
It needs to be cleaned of any things that can cause problems.

And why would you need the zend framework?
theres no need for framework if its just gona be basic stuff.
frameworks only work for things like a news system or a forum or just overall website editing through a CMS.

he just wants a basic usersystem with profiles.

To do databases your best bet would be MySQL. its widespread on servers since its the most well known and most used.
You then just need to set the columns, eg, name, username, password and other info along with an ID which makes it easier if your using sessions as it makes it smaller.
Then its down to just using php to get the persons info and echo it out.
so yeah. :)

[iGoD ReleNtLeS]

Well i currently have MySQL, Apache and PHP as part of WAMPserver as i am self hosting this for a while until i get everything sorted, then i will get it hosted professionally once its all up and running smoothly. Its only going to be used lightly say around 10-50 users at the absolute maximum, then just straddlers that dont sign up but access the website.

Yup, all i want is to have a database where people can log in to access certain pages within the website. I currently have version 5.1.36 of MySQL installed, but i have absolutely no idea how to go about this. I dont have a clue where to start, i looked at a couple of tutorials that i googled but they just confused me further.

The first step would be to create the registration form using something along the lines of:


[input name="login id" type="text" value="loginid" size="20"/][br]
[input name="password" type="text" value="password" size="20"/][br]
[input name="email" type="text" value="email" size="50"/][br]
[input type="submit" name="submit" value="submit"/]
[input type="reset" name="reset" value="reset"/]


These would be the validation rules etc. However i feel that im missing something before this and jumping the gun a little. Im More Confused Than A Cow On Astro Turf

[Rhys]

id be happy to help through msn or something and probs could do for a min or two as ive created about 4 usersystems in the past through different ways of PHP. one in basic, 2 in functions and 1 through a preset framework which i found anoying xD.

also for HTML its < not [. thats BBCode which is mainly used in WYSIWYG text boxes.

You first need to add a database user and password into your mysql which can usually be found on the homepage.
Then you need to make a database itself which you connect to the user.
Though PHP you make a config which connects to the user and database.
With this you can then call things from the database to be shown.
The database columns can be made through PHPMyAdmin which i think WAMP has (i know XAMPP has it) so yeah. Its pretty much give it a name and how many characters it can have (varchar30 for usernames and passwords and like varchar500 for info boxes etc (varchar being variable characters meaning it can allow all and any)).
Then its php with $sql = mysql_query() then while($echo = mysql_fetch_array($sql)) to get the details and then to show it echo $echo['username'];
so yeah.
PM your msn if u need more help.

[Mr Banana]

I'm actually building a login script at the moment for my linux gaming website, would be happy to provide you with working code and help :).

1 major security tip as said before, always sanitize stuff people enter into text boxes before you send it to the database. You can do this using "mysql_real_escape_string" or this hand little function:


function quote_smart($value)
{
//Strip slashes
if (get_magic_quotes_gpc())
{
$value = stripslashes($value);
}

//Quote if not integer
if (!is_numeric($value))
{
$value = mysql_real_escape_string($value);
}

return $value;
}


The login form:


<form method="post" action="login.php">
Log into your community account!<br />
<br />
Username:<br />
<input type="text" name="username" /><br />
Password:<br />
<input type="password" name="password" /><br />
<input type="submit" name="login" value="Login" />
</form>


Logging them in (this has been stripped down to be simple!):


// check to make sure they are not empty
if (empty($_POST['username']) || empty($_POST['password']))
{
echo 'You need to enter details into both fields! <a href="login.php">Click here to return to the login page.</a>';
}

// make them safe
$username = quote_smart($_POST['username']);

// md5 the password to check it in the database
$password = md5($_POST['password']);

// check user exists
$sql = "SELECT `user_id`, `username`, `group` FROM `users` WHERE `username` = '{$username}' AND `password` = '{$password}'";
$query = mysql_query($sql);

if ($db->num_rows($query) != 1)
{
echo 'Sorry could not find that user! <a href="login.php">Click here to return to the login page.</a>';
}

// log them in
$user_info = mysql_fetch_assoc($query);

$_SESSION['user_id'] = $user_info['user_id'];
$_SESSION['username'] = $user_info['username'];

echo "Thank you for logging in {$username}. <a href=\"index.php\">Please click here to continue</a>";

[Ian.H]

Its not so much poorly structured sql statements but more the input into them.
It needs to be cleaned of any things that can cause problems.

Bad phraseology used by me there.. meant poorly sanitised SQL statements (input validation etc).


And why would you need the zend framework?
theres no need for framework if its just gona be basic stuff.
frameworks only work for things like a news system or a forum or just overall website editing through a CMS.

You don't _need_ it, it was just a suggestion to make life easy. Frameworks are _not_ "only for things like a news system or forum" etc :rolleye:


he just wants a basic usersystem with profiles.

Right now, sure. What happens if this is developed further in the future? Why not start with a good scalable base?

[ snip ]



Regards,

Ian

[Ian.H]

Here's a function I wrote some time ago to check whether a user is already logged, or to perform a new login. Also checks this data against the database rather than simply relying on cookie / session data and uses a salted password hash for stronger password hashing. Uses Zend Framework though ;) (and a few other custom classes).


/**
* Verify authentication
*
* Check username and password against data in a database
*
* @param string $username
* @param string $passwd
*
* @return bool
*/
public function verify($username = NULL, $passwd = NULL) {
$authData = NULL;
$userId = 0;
$authSuccess = false;

if (!$this->_tableIsOK()) {
return false;
}

// Check for previously logged in session data
if ($this->_session->exists('ds_auth')) {
$authData = unserialize($this->_session->read('ds_auth'));

if (isset($authData['userId']) && (is_numeric($authData['userId']))) {
$userId = (int)$authData['userId'];
}

if (isset($authData['username']) && ($this->_html->stripAll($authData['username']) != '')) {
$username = $this->_html->stripAll($authData['username']);
}

if (isset($authData['passwd']) && ($this->_html->stripAll($authData['passwd']) != '') && (strlen($authData['passwd']) == '32')) {
$passwd = $this->_html->stripAll($authData['passwd']);
}

if (($userId > 0) && ($username != '') && ($passwd != '')) {
// All good, check credentials against database
try {
$res = $this->_db->query('
SELECT `id`,
`username`,
`passwd`,
`salt`,
`lastaccess`,
`sessionid`,
`accesslevel`
FROM `' . $this->_table . '`
WHERE `username` = ' . $this->_db->quote($username) . '
AND `enabled` = 1
AND `sessionid` = ' . $this->_db->quote($this->_session->id())
);

$row = $res->fetch();

if (!is_null($row) && is_object($row)) {
if (md5($passwd . $row->salt) == $row->passwd) {
$authSuccess = true;
}
} else {
// Delete PHPSESSID cookie
$this->_session->kill();
setcookie($this->_session->name(), 'DELETED', (time() - 3600), '/', $_SERVER['HTTP_HOST'], false);
}

unset($row, $res);
} catch (Exception $e) {
if (DEBUG) {
$this->_core->debug($e->getmessage(), __FILE__, __LINE__);
} else {
exit;
}
}
}
} else {
// Perform new auth
try {
$row = $this->_db->fetchRow('
SELECT `id`,
`username`,
`passwd`,
`salt`,
`lastaccess`,
`sessionid`,
`accesslevel`
FROM `' . $this->_table . '`
WHERE `username` = ' . $this->_db->quote($username) . '
AND `enabled` = 1
');

if (!is_null($row) && is_object($row)) {
if (md5(md5($passwd) . $row->salt) == $row->passwd) {
$authSuccess = true;

// Store data in session
$dbData = array(
'userId' => $row->id,
'username' => $row->username,
'passwd' => md5($passwd),
'accessLevel' => $row->accesslevel
);
$this->_session->set('ds_auth', $this->_packAuth($dbData));
unset($dbData);

// Store session ID in db
try {
$res = $this->_db->query('
UPDATE `' . $this->_table . '`
SET `sessionid` = ' . $this->_db->quote($this->_session->id()) . '
WHERE `id` = ' . $row->id
);
} catch (Exception $e) {
if (DEBUG) {
$this->_core->debug($e->getMessage());
} else {
exit;
}
}

// Update last access time
$this->_updateLastAccess($row->id);
} else {
$this->_session->set('errorMsg', 'Invalid credentials.');
}
}
} catch (Exception $e) {
if (DEBUG) {
$this->_core->debug($e->getMessage(), __FILE__, __LINE__);
} else {
exit;
}
}
}

unset($authData);

return $authSuccess;
}


Regards,

Ian