PDO safe inserting data into database

[Mlaaa]

Hi

 

I have a question about managing data from forms and database, to be exact for safe input/output data from form input fields.

 

Do i need some filters to remove code from input if user try to insert ?

 

When i making database table i limiting chars and same in form.

 

Here is a piece of code i use just for test and example :

// connection to database
$dbh = new PDO('mysql:host=localhost;dbname=test123', 'root', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

	// variables to insert into database
	$username 	= $_POST['username'];
	$password 	= $_POST['password'];
	$email 		= $_POST['email'];
	
	// query with prepare statements
	$stmt = $dbh->prepare("INSERT INTO members (username, password, email) VALUES (:username, :password, :email)");
	$stmt->bindParam(":username", $username, PDO::PARAM_STR);
	$stmt->bindParam(":password", $password, PDO::PARAM_STR);
	$stmt->bindParam(":email", $email, PDO::PARAM_STR);
	$stmt->execute();
	$lastId = $dbh->lastInsertId();
	
	// checking if query is passed and data is inserted into dataabse
	if($lastId > 0)
	{
		echo 'Thank u for register.';
	}
	else
	{
		echo 'Something went wrong, please try again.';
	}

[mds1256]

Looks fine to me, using prepared statements does the escaping for you so you don't need to run the input through any functions.

 

There are a couple of ways to secure your form a little better, which validates the inputted data, e.g. function to make sure that the email address is formed correctly and that the password is complex (contains upper and lower case as well as numbers etc).

 

The other thing you could look at is the introduction of form tokens which tell your PHP based on a session that the form was submitted from your website rather than someone just copying your html code and creating a spoofed register form

[Mlaaa]

I know for validation that i must check is user/email already exists, double password check, crypt password, password/username lenght and that kind of stuff im just curious when i take $_POST data from form do i must somehow to secure them before inserting.

[mds1256]

I know for validation that i must check is user/email already exists, double password check, crypt password, password/username lenght and that kind of stuff im just curious when i take $_POST data from form do i must somehow to secure them before inserting.

 

Using PDO and prepared statements negates the use of mysqli_real_escape_string etc as it will automatically escape any characters that would cause issues.

[Jacques1]

This is wrong on multiple levels.

 

First off, a prepared statement has nothing to do with escaping. It's an unrelated mechanism where the data is completely separated from the query. When you create a prepared statement, PHP sends a query template with placeholders to the database system:

INSERT INTO members (username, password, email) VALUES (?, ?, ?);

The database system parses this template and creates a query structure. When you bind values to the parameters and execute the statement, PHP passes those values to the database system in a separate packet. So the data is not part of the query and cannot affect the query in any way.

 

Escaping is much less secure, because it still involves inserting the (escaped) data into the query string. This can fail. For example, if the escape method and the database system use two different character encodings, an attacker may very well be able to inject special characters and manipulate the query.

 

Unfortunately, PDO does use escaping by default; they call it “emulated prepared statement”. To get actual prepared statements, you must change the PDO configuration:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

[mds1256]

This is wrong on multiple levels.

 

First off, a prepared statement has nothing to do with escaping. It's an unrelated mechanism where the data is completely separated from the query.

Sorry didn't make myself fully clear, i used the example of using pdo and prepared statements prevents sql injection. However i did fail to spot that EMULATE_PREPARES was not switched off so good spot.

[Mlaaa]

So what u can suggest to me how do i escape data before i insert them into database ? Because i founded in one CMS some fuctions to escape, or is this bad example of using

function cleartext($text, $bbcode=true, $calledfrom='root') {
	$text = htmlspecialchars($text);
	$text = strip_tags($text);
	$text = smileys($text,1,$calledfrom);
	$text = insertlinks($text,$calledfrom);
	$text = flags($text,$calledfrom);
	$text = replacement($text, $bbcode);
	$text = htmlnl($text);
	$text = nl2br($text);

	return $text;
}

function htmloutput($text) {
	$text = smileys($text);
	$text = insertlinks($text);
	$text = flags($text);
	$text = replacement($text);
	$text = htmlnl($text);
	$text = nl2br($text);

	return $text;
}

function clearfromtags($text) {
	$text = getinput($text);
	$text = strip_tags($text);
	$text = htmlnl($text);
	$text = nl2br($text);

	return $text;
}

function getinput($text) {
	//$text = stripslashes($text);
	$text = htmlspecialchars($text);

	return $text;
}

function getforminput($text) {
	$text = str_replace(array('\r','\n'),array("\r","\n"),$text);
	$text = stripslashes($text);
	$text = htmlspecialchars($text);

	return $text;
}

[Jacques1]

I think I've already told you this several times: You do not apply HTML-escaping or any other manipulation before you insert the data. This effectively damages the input and makes it unusable. For example, the username “I <3 PHP” would become “I <3 PHP”. Good luck doing a text search on that.

 

Escaping is done when you output data, and it must match the specific context. HTML-escaping only works for HTML contexts, shell-escaping only works for shell contexts etc.

 

The functions you've found in that CMS are braindead. You should generally stop using random code from the Internet. Think for yourself.