Skip to content

Instantly share code, notes, and snippets.

@khoand0000

khoand0000/secure_users.sql

Last active December 29, 2015 15:28
Show Gist options
  • Save khoand0000/78cbc7c88f0858419889 to your computer and use it in GitHub Desktop.
Save khoand0000/78cbc7c88f0858419889 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
secure_users.sql
--
-- Table structure for table `users`
-- ref: https://community.linkedin.com/questions/4568/maximum-characters-counts-for-2013.html
-- password, sale use SHA512
--
CREATE TABLE IF NOT EXISTS `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` char(128) NOT NULL,
`salt` CHAR(128) NOT NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`email` varchar(255) NOT NULL,
`phone` varchar(25) DEFAULT NULL,
`address` varchar(1000) DEFAULT NULL,
`company` varchar(100) DEFAULT NULL,
`job` varchar(100) DEFAULT NULL,
`ip` varchar(15) NOT NULL,
`registered` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '1: active',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Raw
user_registration_form.php
<?php
session_start();
if (isset($_SESSION['username'])) {
header('Location: content.php');
exit();
}
// Function to get the client IP address
function get_client_ip() {
$ipaddress = '';
if (getenv('HTTP_CLIENT_IP'))
$ipaddress = getenv('HTTP_CLIENT_IP');
else if(getenv('HTTP_X_FORWARDED_FOR'))
$ipaddress = getenv('HTTP_X_FORWARDED_FOR');
else if(getenv('HTTP_X_FORWARDED'))
$ipaddress = getenv('HTTP_X_FORWARDED');
else if(getenv('HTTP_FORWARDED_FOR'))
$ipaddress = getenv('HTTP_FORWARDED_FOR');
else if(getenv('HTTP_FORWARDED'))
$ipaddress = getenv('HTTP_FORWARDED');
else if(getenv('REMOTE_ADDR'))
$ipaddress = getenv('REMOTE_ADDR');
else
$ipaddress = 'UNKNOWN';
return $ipaddress;
}
if (isset($_POST['submit'])) {
# connect to the database here
$link = mysqli_connect('localhost', 'root', '', 'test');
# Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
/* change character set to utf8 */
if (!mysqli_set_charset($link, "utf8")) {
printf("Error loading character set utf8: %s\n", mysqli_error($link));
}
#check too see what fields have been left empty, and if the passwords match
$error = '';
if (empty($_POST['username'])) {
$error .= 'User Name can\'t be empty<br>';
} else {
# search the database to see if the user name has been taken or not
$query = sprintf("SELECT username FROM users WHERE username='%s' LIMIT 1", mysqli_real_escape_string($link, $_POST['username']));
$result = mysqli_query($link, $query);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
if ($row) {
$error .= 'User Name already exists<br>';
}
mysqli_free_result($result);
}
if (empty($_POST['firstName'])) {
$error .= 'First Name can\'t be empty<br>';
}
if (empty($_POST['lastName'])) {
$error .= 'Last Name can\'t be empty<br>';
}
if (empty($_POST['email'])) {
$error .= 'Email can\'t be empty<br>';
} else {
# search the database to see if the email has been taken or not
$query = sprintf("SELECT username FROM users WHERE email='%s' LIMIT 1", mysqli_real_escape_string($link, $_POST['email']));
$result = mysqli_query($link, $query);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
if ($row) {
$error .= 'Email already exists<br>';
}
mysqli_free_result($result);
}
if (empty($_POST['password'])) {
$error .= 'Password can\'t be empty<br>';
}
if (empty($_POST['rePassword'])) {
$error .= 'You must re-type your password<br>';
}
if (!empty($_POST['password']) && !empty($_POST['rePassword']) && $_POST['password'] != $_POST['rePassword']) {
$error .= 'Passwords don\'t match<br>';
# If all fields are not empty, and the passwords match
if (strlen($error) == 0) {
$ip = get_client_ip();
/* create a prepared statement */
$query = "INSERT INTO users(`username`,`password`,`first_name`,`last_name`,`email`,`phone`,`address`,`company`,`job`,`ip`)
VALUES(?,SHA1(?),?,?,?,?,?,?,?,?)";
if ($stmt = mysqli_prepare($link, $query)) {
/* bind parameters for markers */
mysqli_stmt_bind_param($stmt, "ssssssssss",
$_POST['username'],
$_POST['password'],
$_POST['firstName'],
$_POST['lastName'],
$_POST['email'],
$_POST['phone'],
$_POST['address'],
$_POST['company'],
$_POST['job'],
$ip
);
/* execute query */
if (mysqli_stmt_execute($stmt)) {
# Redirect the user to a login page
header("Location: sign_in.php");
/* close statement and connection */
mysqli_stmt_close($stmt);
/* close connection */
mysqli_close($link);
}
}
exit;
} else {
# echo out each variable that was set from above,
echo $error;
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Registration</title>
</head>
<body>
<!-- Start your HTML/CSS/JavaScript here -->
<form action=" <? echo $_SERVER['PHP_SELF']; ?> " method="post">
<label for="username">Username</label>
<input type="text" name="username" id="username"
value="<?php if (isset($_POST['username'])) echo $_POST['username']; ?>">
<br>
<label for="password">Password</label>
<input type="password" name="password" id="password">
<br>
<label for="rePassword">Re-Type Password</label>
<input type="password" name="rePassword" id="rePassword">
<br>
<label for="firstName">First name</label>
<input type="text" name="firstName" id="firstName"
value="<?php if (isset($_POST['firstName'])) echo $_POST['firstName']; ?>">
<br>
<label for="lastName">Last name</label>
<input type="text" name="lastName" id="lastName"
value="<?php if (isset($_POST['lastName'])) echo $_POST['lastName']; ?>">
<br>
<label for="email">Email</label>
<input type="email" name="email" id="email" value="<?php if (isset($_POST['email'])) echo $_POST['email']; ?>">
<br>
<label for="phone">Contact number</label>
<input type="text" name="phone" id="phone" value="<?php if (isset($_POST['phone'])) echo $_POST['phone']; ?>">
<br>
<label for="address">Address</label>
<input type="text" name="address" id="address"
value="<?php if (isset($_POST['address'])) echo $_POST['address']; ?>">
<br>
<label for="company">Company</label>
<input type="text" name="company" id="company"
value="<?php if (isset($_POST['company'])) echo $_POST['company']; ?>">
<br>
<label for="job">Job</label>
<input type="text" name="job" id="job" value="<?php if (isset($_POST['job'])) echo $_POST['job']; ?>">
<br>
<input type="submit" name="submit" value="Sign up">
</form>
</body>
</html>
Raw
user_registration_form2.php
<?php
session_start();
if (isset($_SESSION['username'])) {
header('Location: content.php');
exit();
}
// Function to get the client IP address
function get_client_ip() {
$ipaddress = '';
if (getenv('HTTP_CLIENT_IP'))
$ipaddress = getenv('HTTP_CLIENT_IP');
else if(getenv('HTTP_X_FORWARDED_FOR'))
$ipaddress = getenv('HTTP_X_FORWARDED_FOR');
else if(getenv('HTTP_X_FORWARDED'))
$ipaddress = getenv('HTTP_X_FORWARDED');
else if(getenv('HTTP_FORWARDED_FOR'))
$ipaddress = getenv('HTTP_FORWARDED_FOR');
else if(getenv('HTTP_FORWARDED'))
$ipaddress = getenv('HTTP_FORWARDED');
else if(getenv('REMOTE_ADDR'))
$ipaddress = getenv('REMOTE_ADDR');
else
$ipaddress = 'UNKNOWN';
return $ipaddress;
}
if (isset($_POST['submit'])) {
# connect to the database here
$link = mysqli_connect('localhost', 'root', '', 'test');
# Check connection
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
/* change character set to utf8 */
if (!mysqli_set_charset($link, "utf8")) {
printf("Error loading character set utf8: %s\n", mysqli_error($link));
}
#check too see what fields have been left empty, and if the passwords match
$error = '';
if (empty($_POST['username'])) {
$error .= 'User Name can\'t be empty<br>';
} else {
# search the database to see if the user name has been taken or not
$query = sprintf("SELECT username FROM users WHERE username='%s' LIMIT 1", mysqli_real_escape_string($link, $_POST['username']));
$result = mysqli_query($link, $query);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
if ($row) {
$error .= 'User Name already exists<br>';
}
mysqli_free_result($result);
}
if (empty($_POST['firstName'])) {
$error .= 'First Name can\'t be empty<br>';
}
if (empty($_POST['lastName'])) {
$error .= 'Last Name can\'t be empty<br>';
}
if (empty($_POST['email'])) {
$error .= 'Email can\'t be empty<br>';
} else {
# search the database to see if the email has been taken or not
$query = sprintf("SELECT username FROM users WHERE email='%s' LIMIT 1", mysqli_real_escape_string($link, $_POST['email']));
$result = mysqli_query($link, $query);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
if ($row) {
$error .= 'Email already exists<br>';
}
mysqli_free_result($result);
}
if (empty($_POST['password'])) {
$error .= 'Password can\'t be empty<br>';
}
if (empty($_POST['rePassword'])) {
$error .= 'You must re-type your password<br>';
}
if (!empty($_POST['password']) && !empty($_POST['rePassword']) && $_POST['password'] != $_POST['rePassword']) {
$error .= 'Passwords don\'t match<br>';
# If all fields are not empty, and the passwords match
if (strlen($error) == 0) {
$ip = get_client_ip();
$query = sprintf("INSERT INTO users(`username`,`password`,`first_name`,`last_name`,`email`,`phone`,`address`,`company`,`job`,`ip`)
VALUES('%s',SHA1('%s'),'%s','%s','%s','%s','%s','%s','%s','%s')",
mysqli_real_escape_string($link, $_POST['username']),
mysqli_real_escape_string($link, $_POST['password']),
mysqli_real_escape_string($link, $_POST['firstName']),
mysqli_real_escape_string($link, $_POST['lastName']),
mysqli_real_escape_string($link, $_POST['email']),
mysqli_real_escape_string($link, $_POST['phone']),
mysqli_real_escape_string($link, $_POST['address']),
mysqli_real_escape_string($link, $_POST['company']),
mysqli_real_escape_string($link, $_POST['job']),
mysqli_real_escape_string($link, $ip));
$sql = mysqli_query($link, $query) or die(mysqli_error($link));
# Redirect the user to a login page
header("Location: sign_in.php");
mysqli_close($link);
exit;
} else {
# echo out each variable that was set from above,
echo $error;
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Registration</title>
</head>
<body>
<!-- Start your HTML/CSS/JavaScript here -->
<form action=" <? echo $_SERVER['PHP_SELF']; ?> " method="post">
<label for="username">Username</label>
<input type="text" name="username" id="username"
value="<?php if (isset($_POST['username'])) echo $_POST['username']; ?>">
<br>
<label for="password">Password</label>
<input type="password" name="password" id="password">
<br>
<label for="rePassword">Re-Type Password</label>
<input type="password" name="rePassword" id="rePassword">
<br>
<label for="firstName">First name</label>
<input type="text" name="firstName" id="firstName"
value="<?php if (isset($_POST['firstName'])) echo $_POST['firstName']; ?>">
<br>
<label for="lastName">Last name</label>
<input type="text" name="lastName" id="lastName"
value="<?php if (isset($_POST['lastName'])) echo $_POST['lastName']; ?>">
<br>
<label for="email">Email</label>
<input type="email" name="email" id="email" value="<?php if (isset($_POST['email'])) echo $_POST['email']; ?>">
<br>
<label for="phone">Contact number</label>
<input type="text" name="phone" id="phone" value="<?php if (isset($_POST['phone'])) echo $_POST['phone']; ?>">
<br>
<label for="address">Address</label>
<input type="text" name="address" id="address"
value="<?php if (isset($_POST['address'])) echo $_POST['address']; ?>">
<br>
<label for="company">Company</label>
<input type="text" name="company" id="company"
value="<?php if (isset($_POST['company'])) echo $_POST['company']; ?>">
<br>
<label for="job">Job</label>
<input type="text" name="job" id="job" value="<?php if (isset($_POST['job'])) echo $_POST['job']; ?>">
<br>
<input type="submit" name="submit" value="Sign up">
</form>
</body>
</html>
Raw
users.sql
--
-- Table structure for table `users`
-- ref: https://community.linkedin.com/questions/4568/maximum-characters-counts-for-2013.html
--
CREATE TABLE IF NOT EXISTS `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` char(41) NOT NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`email` varchar(255) NOT NULL,
`phone` varchar(25) DEFAULT NULL,
`address` varchar(1000) DEFAULT NULL,
`company` varchar(100) DEFAULT NULL,
`job` varchar(100) DEFAULT NULL,
`ip` varchar(15) NOT NULL,
`registered` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '1: active',
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment