How to make my PHP/mySQL register more secure?

Question

Possible Duplicate:
Best way to stop SQL Injection in PHP

my question is how can I make register page more secure? On register.html the user adds some data and if in register.php the username exists in the database it tells him to use another username.

Is it poor against SQL Injection ? In the other hand how can I ensure that users will use only alphanumeric chars?

register.php

$sql ="SELECT * FROM $table_name WHERE username= '$_POST[username]'";
$result = @mysql_query($sql,$connection) or die(mysql_error());

//get the number of rows in the result set
$num = mysql_num_rows($result);

//checks it see if that username already exists
if ($num != 0){

echo "<P>Sorry, that username already exists.</P>";
echo "<P><a href=\"#\" onClick=\"history.go(-1)\">Try Another Username.</a></p>";
exit;

}else{
$sql = "INSERT INTO $table_name VALUES
('$_POST[firstname]', '$_POST[lastname]', '$_POST[username]', password('$_POST[password]'), 'Users', '', '', '$pchange', 
'$_POST[email]', '$default_url', '$verify', '')";

See http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php — JJJ, Aug 24 '11 at 09:35
Belongs on [codereview](http://codereview.stackexchange.com/) (but no, it is hideously vulnerable to injection and I'd bet on it being vulnerable to XSS too). — Quentin, Aug 24 '11 at 09:35

score 1 · Answer 1 · answered Aug 24 '11 at 09:36

1

never pass values from the user direct to the DB

have a look at mysql_real_escape_string()

http://php.net/manual/en/function.mysql-real-escape-string.php

answered Aug 24 '11 at 09:36

Flask

4,966
1
20
39

score 0 · Answer 2 · answered Aug 24 '11 at 09:38

Use prepared statements instead of embedding parameters into sql query

    $pdo = new PDO(*pdo parameters here*);
    $sql = "SELECT * FROM $table_name WHERE username= ?";
    $stmt = $pdo->prepare($sql);
    $rslt = $stmt->execute(array($_POST[username]));
    if (!$rslt){
        var_dump($stmt->errorInfo());
    }

    $num = $stmt->rowCount();

    // ................//
    else{
        $sql = "INSERT INTO $table_name VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";
        $stmt_ins = $pdo->prepare($sql);
        $stmt_ins->execute(array(
            $_POST[firstname], 
            $_POST[lastname], 
            $_POST[username],
            password($_POST[password]),
            'Users', '', '', $pchange,
            $_POST[email], $default_url, $verify, ''
        ));
    }

I read that using prepared statements you secure your application against SQL Injection and XSS. Is that correct? — EnexoOnoma, Aug 24 '11 at 09:41

score 0 · Accepted Answer · answered Aug 24 '11 at 09:40

You should use mysql_real_escape_string() to sanitize all POST values and consequently avoid SQL Injection Attacks.

Try using this functions:

<?php
  //This stops SQL Injection in POST vars
  foreach ($_POST as $key => $value) {
    $_POST[$key] = mysql_real_escape_string($value);
  }

  //This stops SQL Injection in GET vars
  foreach ($_GET as $key => $value) {
    $_GET[$key] = mysql_real_escape_string($value);
  }
?>

Stolen from: http://www.php.net/manual/en/function.mysql-real-escape-string.php#92649

How to make my PHP/mySQL register more secure?

3 Answers3