Simple authorization on PHP+MySQL+JavaScript+AJAX

The main purpose of this script is educational. The script shows how you can solve the problem of authorized access to some data. In addition to authorization, the example also implements the forms for viewing, adding, editing and deleting users. Also minimal security is observed – the data is filtered, the types are checked, the passwords are not stored and are not transmitted in the clear. I know that MD5 bruteforce very quickly, so to work, use your own, more reliable algorithms 😉

The database requires only one table:

CREATE TABLE `users` (
  `ID_USERS` int(11) NOT NULL,
  `US_NAME` varchar(30) NOT NULL,
  `US_FIO` varchar(50) NOT NULL,
  `US_PASSMD5` varchar(32) NOT NULL,
  `US_SESSION` varchar(32) DEFAULT NULL,
  `US_INS` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `US_DEL` timestamp NULL DEFAULT NULL
) ;

ALTER TABLE `users`
ADD PRIMARY KEY (`ID_USERS`),
ADD UNIQUE KEY `US_NAME` (`US_NAME`);

ALTER TABLE `users`
MODIFY `ID_USERS` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;COMMIT;

The most important limitation that this structure imposes is one single active session, since there is no other table for storing them. This means that once an entry is made in another browser, the previous session becomes invalid, because we store only the last one (US_SESSION).

The first user should be added directly from the database, since the editor works only for the authorized user:

INSERT INTO `users` (`ID_USERS`, `US_NAME`, `US_FIO`, `US_PASSMD5`) VALUES
(1, 'admin', 'ADMIN', '58d1921b404b7d6e8f5059fb6e720645');

All php scripts are divided into the following files:

  • db.php – Database connection
  • function.php – Support functions that may be needed in different places.
  • login.php – Verification for authorization, and if the user is not authorized, then interruption of access to any information and display of authorization fields.
  • login_.php – Verification of login, password and authorization.

The interface is implemented by the following files:

  • users.php – a list of users.
  • user.php – Interface for viewing and editing user data.
  • user_.php – Processing user editing.

Files for forming a page:

  • header.php – We connect css, library jquery, we form the title and the main menu
  • footer.php – Footer page.
  • main.css – Make out to your liking.

Important:

At the beginning of most scripts there is

include_once 'db.php';

Therefore, not only the connection of the database, but also the verification of the user’s authorization and the transition to authorization if it is not authorized. This scheme prevents access to data of those who are not authorized (of course this is not suitable for those who have public sections available to everyone).
In addition to those java-scripts that connect header.php, each page additionally implements the necessary scripts for it.
All php-code that is accessed via POST contained in files * _.php for semantic data partitioning display and modify the code.
An example of JavaScript that stores some information in the database:

function SaveFIO() {
  $.ajax({
    type: "POST",
    data: ({action: "upd", ID_USERS: ID_USERS, US_FIO: $("#US_FIO").text() }),
    url: "user_.php",
    dataType: "html",
    success: function (data) {
      $("#FIOButton").hide();
    }
  })
}

Similarly, all the functions for sending data via POST are made. In any POST request processing script, it is also natural to check the authorization, since it is built into the DB connection module itself (db.php) and it is difficult to forget it casually.

All the magic of authorization takes place in the modules login.php and login_.php. The login.php module, being called one of the first, simply will not allow the execution of the following modules if it can not confirm the authorization and record the user ID in $ ID_USERS. To do this, it uses $_COOKIE[‘US_SESSION’] и $_COOKIE[‘ID_USERS’], which are searched in the database and if $ID_USERS is found, gets the value and continues execution of the remaining modules.
If the verification module considers that the user is not authorized, it outputs an authorization form and terminates the query execution. Plus this method, unlike the throwing on the authorization page, is that after the autorization the page is simply updated and the user stays where he was, continuing his work.
Authorization performs such a piece of code:

if (isset($_POST['US_NAME'])and isset($_POST['US_PASSMD5'])) {
  $stmt = $pdo->prepare("select ID_USERS from USERS where US_NAME=? and US_DEL is null and US_PASSMD5=?");
  $stmt->execute(array($_POST['US_NAME'], $_POST['US_PASSMD5']));
  $result = $stmt->fetch();
  if ($result) {
    // valid login and pass
    $stmt = $pdo->prepare("update USERS set US_SESSION=? where ID_USERS=?");
    $US_SESSION = md5(date('dd-mm-yyyy').rand().$result['ID_USERS'].$_POST['US_NAME'].$_POST['US_PASSMD5']);
    $stmt->execute(array($US_SESSION, $result['ID_USERS'] ));

    setcookie('US_SESSION', $US_SESSION);
    setcookie('ID_USERS', $result['ID_USERS']);
  } else {
    setcookie('US_SESSION', '');
  }
}

As it is easy to understand in case of successful combination of login and password in the database, a session hash is generated (based on some data and random components), which is immediately entered into the database, wiping the previous session (if it was there), and saving the value US_SESSION in the cookies User’s browser.

Download this example (do not forget to create DB “DOC” and user admin) – php_auth_sample.rar (5kb)

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *