4 replies to this topic

[Padawan]

Hello everybody!

I've been following the CODECALL forums for some time now. Finally, the time has come to register and be a part of the community.

I'm a newbie PHP coder, working on my very first datebase driven application. The first step was the registration script which I finally got it working, but I have two problems that I want to share, and possibly, get some guidance on how to solve them. The first is, as the title says, the prevention of SQL injections. I understand the concept, but I have a hard time implementing it in my code, which you can see below.

The another question, which is less important until I prevent SQL injection is the registering itself. It does check if the username exists, but somehow I can store same usernames in the database, which both are different by their user_id. How can I stop this?

Anyway, here's the code. Any help is appreciated.

<?php


// Connection with databse


$con=mysql_connect ("localhost", "root", "password");

mysql_select_db ("thedatabase");


// Storing the values submitted by form


$username= strip_tags($_POST['username']);

$pass= strip_tags($_POST['password']);

$password=md5($pass);

$email= strip_tags($_POST['email']);


// Checking if the username is already in use


$queryuser=mysql_query ("SELECT * FROM users WHERE username='$username' ");

$checkuser=mysql_num_rows($queryuser);


if ($checkuser !=0)

{

	echo "Sorry, ".$username." is already been taken.";

}


// A query that inserts user into databse


$insert_user=mysql_query ("INSERT INTO users (username, password, email) VALUES ('$username', '$password', '$email')" );


if ($insert_user)

{

	echo "Registration successful";

}


else 

{

	echo "Error in registration";

}



?>

Thank you in advance!

[WingedPanther]

MySQL :: MySQL 5.0 Reference Manual :: 19.8.3.53 mysql_real_escape_string()

[Padawan]

Thank you, WingedPanther. :)

The concept is clear to me, but as I said, I'm having trouble implementing it in the code I posted.

It should go somewhere here?

$username= strip_tags($_POST['username']);

$pass= strip_tags($_POST['password']);

$password=md5($pass);

$email= strip_tags($_POST['email']); 

So, now I will have something like this?

$username = mysql_real_escape_string($_POST['username']); 

It's kinda confusing. :worry:

[Alexander]

Yes, you can do it like that for each user input. You can as well wrap strip tags within the escaping function if that specific filtering is important to you:

... = mysql_real_escape_string( strip_tags( ... ) );

If you wish for a little more modern approach, you can always use the MySQLi (i for improved) class to not have to escape at all:

$mysqli = new mysqli("localhost", "user", "pass", "database");

//...

$name = strip_tags("O'Reilly");


/* create a prepared statement, with placeholder parameter "?" */

if ($stmt = $mysqli->prepare("SELECT uid FROM tbl_users WHERE Name = ?")) {


    /* bind, "s" for string */

    $stmt->bind_param("s", $name);


    /* execute query */

    $stmt->execute();


    //or possibly with chaining: $stmt->bind_param("s", $name)->execute();


    /* store "uid" from the SELECT query in to a new variable: $uid */

    $stmt->bind_result($uid);



    /* fetch value from the statement object */

    $stmt->fetch();


    print "$name's uid is $uid";


    /* close statement */

    $stmt->close();

}


/* close connection */

$mysqli->close();

It takes a little more writing, however no matter what the user's name is (O'Reilly with an apostrophe here) it will be separate from the query and cannot effect it. This prevents subtle exploits in how PHP renders strings and can be an effective blanket.

PHP: The MySQLi Extension Function Summary - Manual

PDO is another similar class, that I personally prefer. I've even got a PDO tutorial about it. It is always good to look at all your options.

Quote

another question, which is less important until I prevent SQL injection is the registering itself. It does check if the username exists, but somehow I can store same usernames in the database, which both are different by their user_id. How can I stop this?

Your logic currently is as follows:

• Does the name exist?
  
• No: Continue
  
• Yes: Warn, Continue
  
• Commit user to database

Between those two, you need to have the script not commit the user if a warning occurs. These can be done with if/else statements, or redirect the user back with a warning to chose another name (that can be left up to you.)

Alexander.

Edited by Alexander, 27 November 2011 - 07:35 PM.

[Padawan]

Thank you, guys, I fixed it. :)