I suggested to another community user here, to use mysql & php as an alternative to firebase in order to authenticate users. Thought I had better work up a solution to prove a point!
This demonstration simply handles the registration and login of users within an AI2 app, using mysql as a backend. Passwords are hashed. The developer must handle the activities of the user within the app, because that is all it does! (register and login). It can of course then be used to allow a logged in user to send queries to a mysql database, or to perform other functions that may require a login.
Assumptions are made that you have an online server setup, and are comfortable with server configuration, file locations and permissions on the server.
I have used separate php files for each activity with mysql, but they could all be amalgamated into one file. They all make use of prepared statements to help protect from sql injection.
Setup
- you will need a working online http server, with php running and mysql all setup. It will help if you have phpmyadmin running too.
- you will need to create a database on mysql, or use an existing one.
- you will need to create a table, I called mine
usersand configured it as follows:
- now create and upload to your server the following php files:
ai2_config.php
<?php
/* Database credentials */
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'your username');
define('DB_PASSWORD', 'your password');
define('DB_NAME', 'dbName');
/* Attempt to connect to MySQL database */
$link = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);
// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>
ai2_register.php
<?php
require_once "ai2_config.php";
$usercheck = $_POST['user'];
$password = $_POST['pass'];
$stmt = $link->prepare("SELECT id FROM users WHERE username = ?");
$stmt->bind_param("s", $param_user);
$param_user = $usercheck;
$stmt->execute();
if ($stmt->num_rows == 1) {
echo "taken";
} else{
$username = $usercheck;
}
$stmt->close();
if(isset($username)){
$stmt = $link->prepare("INSERT INTO users (username, password) VALUES (?, ?)");
$stmt->bind_param("ss", $param_username, $param_password);
$param_username = $username;
$param_password = password_hash($password, PASSWORD_DEFAULT);
$stmt->execute();
$last_id = $link->insert_id;
echo '[' . $last_id . ', "' . $username . '"]';
$stmt->close();
$link->close();
}
?>
ai2_login.php
<?php
require_once "ai2_config.php";
$usercheck = $_POST['user'];
$password = $_POST['pass'];
$stmt = $link->prepare("SELECT id,username,password FROM users WHERE username = ?");
$stmt->bind_param("s", $param_user);
$param_user = $usercheck;
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($id,$username, $hashed_password);
if ($stmt->num_rows == 1) {
while($stmt->fetch()) {
if(password_verify($password, $hashed_password)){
echo "log";
} else {
echo "Invalid password";
}
}
} else{
echo "Invalid username";
}
$stmt->close();
?>
This is reasonably secure, requiring the current password in order to change to a new password
ai2_change-password.php
<?php
require_once "ai2_config.php";
$usercheck = $_POST['user'];
$password = $_POST['pass'];
$newpassword = $_POST['newpass'];
$stmt = $link->prepare("SELECT password FROM users WHERE username = ?");
$stmt->bind_param("s", $param_user);
$param_user = $usercheck;
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($hashed_password);
if ($stmt->num_rows == 1) {
while($stmt->fetch()) {
if(password_verify($password, $hashed_password)){
$username = $usercheck;
} else {
echo "Invalid password";
}
}
} else{
echo "Invalid username";
}
$stmt->close();
if(isset($username)){
$stmt = $link->prepare("UPDATE users SET password = ? WHERE username = ?");
$stmt -> bind_param("ss", $param_newpassword, $param_user);
$param_newpassword = password_hash($newpassword, PASSWORD_DEFAULT);
$param_user = $username;
$stmt->execute();
if($stmt->affected_rows == 1){
echo "password updated";
} else {
echo "update failed";
}
$stmt->close();
$link->close();
}
?>
ai2_reset-password.php
This is less secure, as an unscrupulous user could guess the username and id, but could be used when a then current password has been forgotten
<?php
require_once "ai2_config.php";
$usercheck = $_POST['user'];
$id = $_POST['id'];
$newpassword = $_POST['newpass'];
$stmt = $link->prepare("SELECT id FROM users WHERE username = ?");
$stmt->bind_param("s", $param_user);
$param_user = $usercheck;
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($foundid);
if ($stmt->num_rows == 1) {
while($stmt->fetch()) {
if($id == $foundid){
$username = $usercheck;
} else {
echo "Invalid Credentials";
}
}
} else{
echo "Invalid Details";
}
$stmt->close();
if(isset($username)){
$stmt = $link->prepare("UPDATE users SET password = ? WHERE username = ?");
$stmt -> bind_param("ss", $param_newpassword, $param_user);
$param_newpassword = password_hash($newpassword, PASSWORD_DEFAULT);
$param_user = $username;
$stmt->execute();
if($stmt->affected_rows == 1){
echo "password updated";
} else {
echo "update failed";
}
$stmt->close();
$link->close();
}
?>
ai2_delete-user.php
<?php
require_once "ai2_config.php";
$usercheck = $_POST['user'];
$password = $_POST['pass'];
$stmt = $link->prepare("SELECT id,username,password FROM users WHERE username = ?");
$stmt->bind_param("s", $param_user);
$param_user = $usercheck;
$stmt->execute();
$stmt->store_result();
$stmt->bind_result($id,$username, $hashed_password);
if ($stmt->num_rows == 1) {
while($stmt->fetch()) {
if(password_verify($password, $hashed_password)){
$username = $usercheck;
} else {
echo "Invalid password";
}
}
} else{
echo "Invalid username";
}
$stmt->close();
if(isset($username)){
$stmt = $link->prepare("DELETE FROM users WHERE username = ?");
$stmt->bind_param("s", $param_user);
$param_user = $username;
if ($stmt->execute()) {
echo "user: " . $username . " deleted";
} else {
echo "Delete Failed";
}
}
$stmt->close();
$link->close();
?>
- build your app, here are my blocks
- run and test your app
AIA
You will need to add your own domain path...with a / at the end
logregsysMySQLFinal.aia (97.4 KB)
Check the About App Screen for more info