SQL PHP Syntax

[Hazukiy]

Hi, I'm just wondering but if my webhost has SQL version 5.1, what are the syntax of that for PHP login & register forms? So like 

 

$q = "INSERT INTO `Table1` (`username`,`password`,`email`) " 
    ."VALUES ('".$_POST["username"]."', " 
    ."PASSWORD('".$_POST["password"]."'), " 
    ."'".$_POST["email"]."')";

 

 

Would this be the right use of syntax? I'm having a few problems with making a clean and safe php login and register form. 

 

Thanks.

[Hazukiy]

UPDATE:

 

This is what I have at the moment.

 

<?php 
define('SALT_CHARACTERS', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789');
	
function generate_salt() {
	$salt = '';
		
	for($i = 0; $i < 21; $i++) {
		$salt .= substr(SALT_CHARACTERS, mt_rand(0, strlen(SALT_CHARACTERS) - 1), 1);
	}
	
	return $salt;
}

$errors = array();

if(isset($_POST['firstname']) && isset($_POST['lastname']) && isset($_POST['username']) && isset($_POST['email']) && isset($_POST['password'])) {
    require_once 'dbConfig.php';
	
	$fullname = $_POST['firstname'];
	$email = strtolower(trim($_POST['email']));
	$password = $_POST['password'];
	
	if($firstname == '') {
		$errors[] = 'Please enter your firstname.';
	}
	
	if($lastname == '') {
		$errors[] = 'Please enter your lastname.';
	}
	
	if($email == '') {
		$errors[] = 'Please enter an email address.';
	}
	
	if($username == '') {
		$errors[] = 'Please enter a username.';
	}
	
	if($password == '') {
		$errors[] = 'Please enter a password.';
	} elseif(strlen($password) < 6) {
		$errors[] = 'Your password must be at least 6 characters long.';
	}
	
	if(count($errors) === 0) {
		$passwordHash = crypt($password, '$2y$12$' . generate_salt());
		
		$query = $link->prepare('INSERT INTO users VALUES(\'\', :firstname, :secondname, :username, :email, :password, \'0\')');
        $query->execute(array(
            ':firstname' => $firstname,
			':secondname' => $secondname,
			':username' => $username,
            ':email' => $email,
            ':password' => $passwordHash,
        ));	
	}
}	

[Jessica]

You shouldn't use the PASSWORD function in MySQL, it's not for that. You should use the php crypt() function, or something like PHPass. (Which I see you did in your second post, so that's good).

 

Your salt function could be simplified by using an array, which you can build using array_merge(range('A', 'Z'), range('a', 'z'), range(0,9)).

 

I would trim everything EXCEPT the password, but not just email. Names too.

 

Edit: Also you need to work out your logic here:

$fullname = $_POST['firstname'];
	
	if($firstname == '') {
		$errors[] = 'Please enter your firstname.';
	}
	
	if($lastname == '') {
		$errors[] = 'Please enter your lastname.';
	}
	

Edited March 23, 2013 by Jessica

[Hazukiy]

How's this?

 

 

if(isset($_POST['firstname']) && isset($_POST['lastname']) && isset($_POST['username']) && isset($_POST['email']) && isset($_POST['password'])) {
    require_once 'dbConfig.php';
	
	$fullname = strtolower(trim($_POST['firstname']));
	$secondname = strtolower(trim($_POST['secondname']));
	$username = strtolower(trim($_POST['username']));
	$email = strtolower(trim($_POST['email']));
	$password = $_POST['password'];
	
	if($firstname == '') {
		$errors[] = 'Please enter your firstname.';
	}
	
	if($lastname == '') {
		$errors[] = 'Please enter your lastname.';
	}
	
	if($email == '') {
		$errors[] = 'Please enter an email address.';
	}
	
	if($username == '') {
		$errors[] = 'Please enter a username.';
	}
	
	if($password == '') {
		$errors[] = 'Please enter a password.';
	} elseif(strlen($password) < 6) {
		$errors[] = 'Your password must be at least 6 characters long.';
	}
	
	if(count($errors) === 0) {
		$passwordHash = crypt($password, '$2y$12$' . generate_salt());
		
		$query = $link->prepare('INSERT INTO users VALUES(\'\', :firstname, :secondname, :username, :email, :password, \'0\')');
        $query->execute(array(
            ':firstname' => $firstname,
			':secondname' => $secondname,
			':username' => $username,
            ':email' => $email,
            ':password' => $passwordHash,
        ));	
	}
}	
	
	
?>

 

 

Also, what do you mean by work out my logic?

 

Thanks.

[Jessica]

Look at the lines I posted. They don't make any sense.

 

$fullname = $_POST['firstname']?

$secondname = $_POST['lastname']?

Then later you use firstname and lastname, which are both undefined.

Edited March 23, 2013 by Jessica

[Hazukiy]

Oh, sorry forgot to post all the code

 

<form action="register.php" method="POST">
	<fieldset>
	<label for="firstname">First name:</label>
	<font color="red">*</font><input class="GeneralForm" type="text" name="firstname" id="firstname" maxlength="30"><br>
	<br>
	<label for="lastname">Last name:</label>
	<font color="red">*</font><input class="GeneralForm" type="text" name="lastname" id="lastname" maxlength="30"><br>
	<br>
	<label for="username">Username:</label>
	<font color="red">*</font><input class="GeneralForm" type="text" name="username" id="username" maxlength="20"><br>
	<br>
	<label for="email">Email:</label>
	<font color="red">*</font><input class="GeneralForm" type="text" name="email" id="email" maxlength="30"><br>
	<br>
	<label for="password">Password:</label>
	<font color="red">*</font><input class="GeneralForm" type="password" name="password" id="password" maxlength="20"><br>
	<br>
	<button type="submit" name="submit" class="InputButton" value="Submit">Submit</button>
	</fieldset>
	</form>

[Jessica]

$a = $_POST['a1'];
if($a1 == ''){
   echo $a1;
}

This is what you did. What do you think will happen every time?

Hint: You'll get a PHP notice if you have error reporting set to -1.

[Hazukiy]

 

$a = $_POST['a1'];
if($a1 == ''){
   echo $a1;
}

This is what you did. What do you think will happen every time?

Hint: You'll get a PHP notice if you have error reporting set to -1.

 

 

Ahah I just noticed, thanks xD

[Hazukiy]

Ok so I'm getting some errors with my Query, would you know how I can lay it out other than the way I've done it? 

 

 

 

if(count($errors) === 0) {
		$passwordHash = crypt($password, '$2y$12$' . generate_salt());
		
		$query = $link->prepare('INSERT INTO users VALUES(\'\', :firstname, :lastname, :username, :email, :password, \'0\')');
        $query->execute(array(
            ':firstname' => $firstname,
			':lastname' => $lastname,
			':username' => $username,
            ':email' => $email,
            ':password' => $passwordHash,
        ));	
		
		$lastId = $link->lastInsertId();
	}