AJAX - Request MySQL data through PHP

Suppose we need to get password (actually hash value in database) of a specified user, how we can make that?

We have a form to get user name input and have a button associated with function getResult().

 


    Admin Name
    


    

getResult()

function getResult() {
    var xmlhttp = new XMLHttpRequest();
    var name = document.getElementById("name").value;
    xmlhttp.onreadystatechange = function() {
        if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
            setTimeout(function() {document.getElementById("result").innerHTML = xmlhttp.responseText;}, 1000);
        } else {
            document.getElementById("result").innerHTML = "Loading...";
        }
    }
   
    xmlhttp.open("POST", "getuserhash.php");
    xmlhttp.setRequestHeader('Content-Type', 'application/x-www-form-urlencoded');
    xmlhttp.send("name=" + name);
}

getuserhash.php

include_once('connection.php');

$name = $_POST['name'];

$query = $pdo->prepare("SELECT user_password FROM user WHERE user_name = ?");
$query->bindValue(1, $name);
$query->execute();
$data = $query->fetch();

echo $data["user_password"];

It works!

Use jQuery to rewrite getResult()

        
function getResult() {
    var name = $("#name").val();
    $.post("getuserhash.php", {name:name}, 
        function(data) {
            $("#result").html(data);
        }
    );
}

PHP - Register User

In a-simple-cms, we don't have a page that allows to create new user. So we create this page now and use jQuery form validation plugin to validate the input before forwarding the request.

Now the admin login page is

We create a admin register link to the registration page

See the code of admin registration page

if (isset($_POST['name'], $_POST['pass1'])) {

    include_once('../includes/connection.php');
    include_once('../includes/chash.php');

    $chash = new CHash();

    $name = $_POST["name"];
    $password = $_POST["pass1"];
    $phash = $chash->generateHash($password);

    $query = $pdo->prepare("INSERT INTO user (user_name, user_password) VALUES (?, ?)");
    $query->bindValue(1, $name);
    $query->bindValue(2, $phash);

    if($query->execute()){
        echo "admin created!";
    } else {
        echo "creation failed.";
    }

} else {
    // display registration form
}

Include css, jQuery library, jQuery validation plug in and custom validation code.

Code of custom.js

$(document).ready(function() {
    $("#form").validate({
        rules: {
            name: {
                required: true,
                minlength: 5,
                maxlength: 20
            },

            pass1: {
                required: true,
                minlength: 8,
                maxlength: 30
            },

            pass2: {
                required: true,
                minlength: 8,
                maxlength: 30,
                equalTo: "#pass1"
            },

            username: {
                required: true
            },

            password: {
                required: true
            }
        }
    });
});

Registration form

Admin Registration


Admin Name
    


Password
    


Confirm Password
    





back to login

See the form check result

Create admin with name "admin"

Created

Admin entry created in database

Because user_name is primary key, so we cannot create another admin with "admin" again.

All right, now we can login with the admin created

PHP - Store and get date/time value between PHP and MySQL

MySQL has three kinds of date/time attributes: DATETIME, DATE and TIMESTAMP.

• DATETIME starts from '1000-01-01 00:00:00' and ends up with '9999-12-31 23:59:59'. 
• DATE is part of DATETIME: YYYY-MM-DD
• TIMESTAMP starts from '1970-01-01 00:00:01' UTC and ends up with '2038-01-19 03:14:07' UTC. It is still fine today. However, do you still remember Y2P? Will TIMESTAMP be Y2K38P?

Generally speaking, you can use DATETIME for date like birthday, and use TIMESTAMP for anything related to current time. For example, when you add an article, you can use TIMESTAMP. 

In addition, since TIMESTAMP is timezone related, you will get a different value if your change your timezone. TIMESTAMP is also particularly useful in logging because it can be told to be updated on INSERTs and UPDATEs.

In summary, DATETIME represents a date (in calendar) and a time (on clock), while TIMESTAMP represents a well defined point in time.

PHP uses time() to return current Unix timestamp and date() to format a local time/date.

So, suppose we have a DATETIME column in MySQL, how we should store and get the value between PHP and database?

To Store

    
    //
    $datetime = date('Y-m-d h:i:s', time());
    // 2013-05-28 02:10:14

To Get - use strtotime() to parse any English textual datetime description into a Unix timestamp, and then use date() to change the timestamp to any format you would like to show.

    
    //
    $timestamp = strtotime($article["article_time"]);
    $time = date("l jS, F Y", $timestamp); 
    // Tuesday 28th, May 2013

Frequently used format in date().

d	Day of the month	01 to 31
j	Day of the month	1 to 31
S	English ordinal suffix for the day of the month	st, nd, rd or th. Works well with j
D	A textual representation of a day	Mon to Sun
l	A full textual representation of the day	Sunday to Saturday
m	Numeric representation of a month	01 to 12
n	Numeric representation of a month	1 to 12
M	A short textual representation of a month	Jan to Dec
F	A full textual representation of a month	January to December
Y	A full numeric representation of a year	1999 or 2003
y	A two digit representation of a year	99 or 03
a	Lowercase Ante meridiem and Post meridiem	am or pm
A	Uppercase Ante meridiem and Post meridiem	AM or PM
h	12-hour format of an hour	01 to 12
H	24-hour format of an hour	00 to 23
i	Minutes with leading zeros	00 to 59
s	Seconds, with leading zeros	00 to 59

Set your local timezone with date_default_timezone_set()

//
date_default_timezone_set('Australia/Adelaide');
//

Find all supported timezones at timezones.

PHP - Store Password

Code snip of password verification when we use plain text. (should never happen...)

            $query = $pdo->prepare("SELECT * FROM user WHERE user_name = ? and user_password = ?");
            $query->bindValue(1, $username);
            $query->bindValue(2, $password);
            $query->execute();

            $num = $query->rowCount();

            if ($num == 1) {
                // correct
                $_SESSION['logged_in'] = true;
                header('Location: index.php');
                exit();
            } else {
                // incorrect
                $error = "Incorrect Details.";
            }

All right, let us add some protection to password with MD5. Verification code is the same, and just call md5 to user input.

//
$password = md5($_POST['password']);
//

MD5 is one of the cryptographic hashing methods to make it difficult to get the original password. The method looks perfect but it is vulnerable to Brute Force (hash collision) and Rainbow Table.

Rainbow Table is pretty cool and it not only works to MD5, but all hash methods, such as SHA1.

So, what we should do? In general, we should

• Use slow hashing algorithm (bcrypt)
• Use random salt
• Recreate hash every time when user login

Hash generation code

    public function generateHash($password) {
        $cost = 10;
        $salt = strtr(base64_encode(mcrypt_create_iv(16, MCRYPT_DEV_URANDOM)), '+', '.');
        $salt = sprintf("$2a$%02d$", $cost) . $salt;
        $hash = crypt($password, $salt);
        return $hash;
    }

Verification code (regenerate hash when login)

            $query = $pdo->prepare("SELECT user_password FROM user WHERE user_name = ?");
            $query->bindValue(1, $username);
            $query->execute();
            $data = $query->fetch();

            if (crypt($password, $data["user_password"]) == $data["user_password"]) {
                $_SESSION['logged_in'] = true;
                header('Location: index.php');

                $newhash = $chash->generateHash($password);
                $query= $pdo->prepare("UPDATE user SET user_password = ? WHERE user_name = ?");
                $query->bindValue(1, $newhash);
                $query->bindValue(2, $username);
                $query->execute();
                exit();
            } else {
                $error = "Incorrect Details.";
            }

PHP - A simple CMS - Online Library

Content Management System (CMS) is a computer system that allows the user to create, edit, delete, and show whatever information they are interested. CMS is often working as a website developed with database, back-end and front-end scripting language.

I have created a simple CMS - Online Library, to simply describe what a CMS looks like.

The system is developed by PHP and MySQL, and running on Apache. Actually, I am using WAMP which you can find at wampserver to deploy the environment.

The system includes:

• List and show articles (everyone)
• Add, edit and delete articles (admin)
• Database and CSS files

See code structure and database tables structure.

See the page and behaviour