Need a Multi-User Login Example with permission in PHP and SQLite

PHP
1

Hi everyone.
I need a Multi-User Login Example with user permissions, for example the admin can only access admin page and other users the user page.
Can anyone provide a sample to start. I want to use SQLite as backend database.
Thanks for the help

2

At the header of the page that don’t you want to see the user put a simple code something like this:

If($user_role==user){
header("Location: ‘here put a page that you want to go the user’ ");

3

Thanks for the quick reply but I need a complete example with code. I am new to database handling with php specially sqlite.
I want to use Sqlite as it is server-less and a good option to use in PHP Desktop APP.
Thanks to you

4

You mean you want someone to write a log-in system for you?

5

Not exactly, I need a sample to fetch data from SQLite and match it with a user submitted data from a login form and check it with the users table stored in SQLite.
The Table structure might be something like

USERID USER_PASSWORD USER_ROLE

6

What are the table shemas for those?

7

there’s nothing special about using SQLite compared to other DBMS - take any tutorial that fits and replace the PDO DNS. just as this:

8

Here you go: http://symfony.com/doc/current/security.html

Come back if you have any questions.

9

Hi everyone, previously I was using a MySQL simple login system.

DB.PHP (For MySQL DB Connection)

<?php
function get_db(){
	$db_name="login"; // Database name
	$host="localhost";
	$username="root";
	$password="the_password";
	$db = new PDO("mysql:host=$host;dbname=$db_name;charset=utf8", $username, $password);
	return $db;
}
?>

and here’s the check.php

<?php
require_once("db.php");
function check_input($r){
	$r=trim($r);
	$r=strip_tags($r);
	$r=stripslashes($r);
	$r=htmlentities($r);
	$r=mysql_real_escape_string($r);
	return $r;
	}
if (isset($_POST['uname'],$_POST['pwd'])){
	
	$u=check_input($_POST['uname']);
	$p=md5(check_input($_POST['pwd']));
	try{
	$db=get_db();
	$stmt=$db->prepare("SELECT * FROM login_details WHERE id=? && password=?");
	$stmt->execute(array($u,$p));
	$r=$stmt->fetch(PDO::FETCH_ASSOC);
	if($r){
		session_start();
		$access_level=$r['access_level'];
		$_SESSION['id']=$r['id'];
		$_SESSION['access_level']=$access_level;
		if ($access_level==0){
			header("Location:user.php");
			}
		else if($access_level==1){
			header("Location:admin.php");
			}
		}
	else{
		header("Location:index.php?err=1");
		}
	}
	catch(PDOException $e){
		die("Database error: ".$e->getMessage());
	}
}
else{
	header("Location:index.php");
	}
?>

Code to create the Database for MySQL is as follows

<?php
$db_name="login"; // Database name
$host="localhost";
$username="root";
$password="7x9Z3px2Z0Zero@92_AM";
$query="CREATE TABLE login_details(
		id VARCHAR(15) NOT NULL,
		password VARCHAR(32) NOT NULL,
		access_level INT(10) NOT NULL,
		PRIMARY KEY(id)
	)";
try{
	$db = new PDO("mysql:host=$host", $username, $password);
	$db->exec("CREATE DATABASE $db_name");
	$db->query("USE $db_name");
	$db->query($query);
}
catch(PDOException $e){
	die("Database error: ".$e->getMessage());
}
?>

The Table structures are same for the SQLite but still I’m getting errors. Can anyone rectify how to use the fetch in SQLite PDO and check the input.
Thanks

10

Your code has some serious security and design flaws

  • the check_input function is useless and harmful. mysql_real_escape_string, seriously?
  • get_db() function will create a new connection every time it is called, that renders it either harmful or useless
  • MD5 for passwords has been considered insecure long time ago
  • SQL error is revealed to the user. Although it is very common for PHP learners to consider themselves the only users of their site, it is not [hopefully] so. Thus errors have to be shown to the programmer, not user.
  • There are no exit()'s after redirects. If the same approach will be used on the protected page, it will protect nothing, as the code will continue to execute and show the protected content to anyone who would like to stay on the page instead of following the redirect.
3 Likes
11

I’m a newbie to PHP and still learning. I’ve been using Object Pascal to develop database application with both server and client ends. If there is an example of a secure login system written in SQLite, It will be nice so that I can study it and follow along as I’m very short on time.

12

Believe me, learning how to take advantage of what PDO has to offer now will be well worth the time spent.

Maybe not a login system per se, but the examples of prepared statements should get you off to a good start.
Those and a read of how SESSIONs work.

http://php.net/manual/en/book.session.php

1 Like
13

as to @colshrapnel here are a few enhancements

  1. skip the check_input function, just use prepared statements (prepare() and execute() with placeholders) as you already did
  2. instantiate PDO once at the top of your script and use the $db variable. if you need it within a function/class, provide it as an argument
  3. use password_hash() instead of md5
  4. do not die() on an exception, better log it within a file
  5. self-explaining: use exit() after redirects
2 Likes
14

Thanks a ton chorn, I’ll try these and report back in case of any problems.

15

Thanks dear, I’ll take a look

16

In order to improve, take these steps:

  1. get rid of check_input function

  2. change db.php definition as follows

     <?php
 function get_db(){
     static $db;
     if (!$db) {
         $db_name="login"; // Database name
         $host="localhost";
         $username="root";
         $password="the_password";
         $db = new PDO("mysql:host=$host;dbname=$db_name;charset=utf8", $username, $password);
     }
     return $db;
 }

  3. Change the users table definition making password field 60 characters length

  4. Store passwords using password_hash function

  5. get rid of try…catch statement

  6. Verify a user as shown in the example I wrote here: Authenticating a user using PDO and password_hash()

  7. add exit after each Location header call in your code

1 Like
17

Thanks very much sir

18

Don’t blindly copy-paste colshrapnel’s code example.

You’ll want to change this line accordingly

$db = new PDO("mysql:host=$host;dbname=$db_name;charset=utf8", $username, $password);

Re the out-dated check_input()

Newer HTML5 has some nice input stuff that can go far, but check out browser support

And check out PHP’s filters

http://php.net/manual/en/book.filter.php

19

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.