...

View Full Version : preparing user input for insertion.



LearningCoder
07-24-2012, 06:12 AM
Hello,

Struggling with user input here and trying to figure out the best way to prepare data for insertion to a database. For instance what is the best way to escape the data to protect the database?

Been googling for an hour now, I have come across these functions: quotemeta(), addslashes() and htmlspecialchars() but I'm getting confused on the best one to use.

I created a simple function which takes the user input and then sent the data to htmlspecialchars() but when I enter the relative characters, they are not being converted. When using addslashes() it only adds slashes before double or single quotes but the video I have watched states it should also cover more characters...

Just looking for some advice really...I ended up deleting the code I had and decided to start from scratch. I was told I should let the user enter any data they want but just to make sure to escape it before I insert it into the database.

Any advice guys?

Kind regards,

LC.

paddyfields
07-24-2012, 11:39 AM
You should be using mysql_real_escape_string() to help prevent SQL injections.

I use a combination of that and addslashes()

Fou-Lu
07-24-2012, 02:45 PM
Step 1 is always stripslashes if you are runing magic quotes (gone as of 5.4). Never use addslashes especially in combination with mysql_real_escape_string.
strip_tags can be used if you want to remove HTML completely.
Finally use binding with PDO or MySQLi instead of the old mysql library.


if (ini_get('magic_quotes_gpc'))
{
$_POST['enterdata'] = stripslashes($_POST['enterdata']);
}

if ($stmt = $conn->prepare('INSERT INTO table (field) VALUES (?)'))
{
$stmt->bind_param('s', $_POST['enterdata']);
$stmt->execute();
$stmt->close();
}

Simple as that.
Don't forget to do any validation and verification you need to before you insert.
htmlspecialchars or htmlentities should be used in display and not in storage. This will help get around a few charset issues with characters that are available in htmlentities that would therefore become unsearchable (unless you go by the htmlentity of it of course) in the storage if converted prior to storage.

paddyfields
07-24-2012, 03:23 PM
sorry i did actually mean stripslashes

What's wrong with using mysql_real_escape_string()?

Fou-Lu
07-24-2012, 03:36 PM
Nothing, if you are using mysql library and should be used as the final step before insertion.
But mysql library is getting really old (I see they've finally added notes indicating that its become obsolescent and its use is discouraged), so you should opt for PDO or MySQLi for the new connectivity type. And since both can make use of prepared statements, its silly to execute a raw statement when prepared are available and dealing with variable data.

So the mysql is still available, and there is no indication that it's hit a deprecated stage, but has certainly hit the obsolescent phase (awhile ago actually). So you really should avoid using it for any current and new projects as it will eventually disappear or should be used only on support for legacy applications.

Keleth
07-24-2012, 03:41 PM
Its definitely worth switching over to PDO. After some use, you'll find its easier to use, more convenient, and as you write larger apps, custom functions/workarounds you had to write to work with the regular MySQL results are already taken into account with PDO. If nothing else, it sanitizing the data for you is great, leaving you only to worry about validation and not special characters.

paddyfields
07-24-2012, 04:30 PM
Ah man, PDO looks complicated. I see some long nights of coding ahead :rolleyes:

Keleth
07-24-2012, 04:36 PM
Its not! I promise you!

Its object oriented, which if you're not used to, can be a bit strange, but it is very simple to use.

paddyfields
07-24-2012, 04:54 PM
(sorry to LearningCoder for jumping on this thread a bit)

As a simple update to bring my code a bit more up to date for now can I simply change all instances of 'mysql' to 'mysqli' ?. Obviously with the future prospect of converting my whole site to PDO.

Keleth
07-24-2012, 05:54 PM
Well, I donno definitively, but I doubt you can do a one for one switch of mysql to mysqli... there are bound to be differences in function names. Plus, the real advantage of switching to mysqli is to use the object side of it, same with PDO. Just changing all your functions to the mysqli equivalents won't help/fix anything.

Fou-Lu
07-24-2012, 06:53 PM
No you can't. Even procedural MySQLi differs from MySQL even though the names are the same with the addition of the i, the argument lists are different. Both mysql and mysqli make use of the same information (connections, resultsets, etc), but mysqli requires that parameter first, while mysql had that parameter as an optional second.

So no, you definitely can't switch. Plus the best part of prepared statements requires rewrite.
These reasons are why I still suggest writing aggregate storage classes. Then all you need to do is rewrite the underlying drivers for it instead of the code that uses it.


Also, mysqli is not global like mysql is. For whatever reason that mysql connection was original determined to be allowed as a global resource is beyond me. At least mysqli fixes that.

paddyfields
07-24-2012, 07:49 PM
Ok, thanks to you both. Very helpful.

LearningCoder
07-25-2012, 09:59 AM
No problem paddy, it was quite interesting to read the replies to this. To be honest, what you guys are talking about here is too hard for me to grasp at the moment but it's always useful to come back to threads like this for when I do get to the next stage in my learning development.

I have another issue at the moment with the isset() function. It keeps executing the else statement to the isset() if statement. For example, if I leave the pass and email form fields blank, the code only echo's the username, when in actual fact it shouldn't execute that at all seeing as though the if statement should be executing and not the else because the variables have not been set?? I've probably missed something so basic. Here is my code:


<?php
session_start();

function check_string($data){
$chars = array('!','?','~','@','"','','$','%','^','&','*','(',')','[',']','#','/','<','>','+','=','-','_','.',',',':',';','{','}','|');
$charlen = count($chars);

for($i=0;$i<$charlen;$i++){
if(strstr($data, $chars[$i])){
$result = "TRUE";
return $result;
}
}
$result = "FALSE";
return $result;
}

$user = $_POST['user'];
$pass = $_POST['pass'];
$email = $_POST['email'];

if($_SERVER['REQUEST_METHOD'] == "POST"){

if(!isset($user, $pass, $email)){

echo "Please fill in ALL of the form fields.";
header("resfresh:5, url=index.php");

}
else{
echo $user."<br>";
echo $pass."<br>";
echo $email."<br>";
}
}
else{
echo "There was an issue with the server <br>";
echo "You will be redirected shortly.";
header("refresh:5, url=index.php");
}
?>


I also tried comparing the variables to 'NULL' but it didn't help at all. As you can see, I am not making use of the check_string() function at the moment but have opted to dis-allow users to enter those characters as a username because I want to keep the usernames to basic characters, otherwise someone could enter: ^%@*~({ as a username, which I definitely do not want.

Can anyone help me out here?

Kind regards,

LC.

paddyfields
07-25-2012, 11:05 AM
try this instead of the isset()



if($user && $pass && $email != NULL){
echo $user."<br>";
echo $pass."<br>";
echo $email."<br>";
}
else
{
echo "Please fill in ALL of the form fields.";
}

Fou-Lu
07-25-2012, 02:27 PM
The isset doesn't work for you simply because you have already created the variables. To get around the notice's you'll receive if the form isn't posted properly simply change it to this:


if(isset($_POST['user'], $_POST['pass'], $_POST['email'])){
$user = $_POST['user'];
$pass = $_POST['pass'];
$email = $_POST['email'];

And use the else clause to redirect.

LearningCoder
07-25-2012, 11:58 PM
I have changed the code to what you have told me Fou-Lu but it is still executing the echo statements...This is an absolute mystery...

My code is:


if($_SERVER['REQUEST_METHOD'] == "POST"){

if(isset($_POST['user'], $_POST['pass'], $_POST['email'])){

$user = $_POST['user'];
$pass = $_POST['pass'];
$email = $_POST['email'];
echo $user."<br>";
echo $pass."<br>";
echo $email;



}
else{

echo "Please fill in ALL of the form fields.";
header("refresh:5, url=index.php");
}
}
else{
echo "There was an issue with the server <br>";
echo "You will be redirected shortly.";
header("refresh:5, url=index.php");
}


Can anyone spot anything wrong?

Regards,

LC.

Fou-Lu
07-26-2012, 05:00 AM
Can you post your HTML form as well please?
Which else are you referring to as well, the check for the request method, or the check for the fields?

LearningCoder
07-26-2012, 09:52 AM
When I execute the code, even if I leave 2 out of 3 form fields blank, it still executes the isset() function as being true, where I thought it would be executing the else statement and redirecting me.

My HTML is:


<form name="regForm" id="regForm" method="POST" action="reg.php">
<fieldset>
<legend>Registration</legend>
<p><label for="myusername">Username:</label><input type="text" name="user" maxlength="20" size="23" /></p>
<p><label for="mypassword">Password:</label><input type="password" name="pass" maxlength="20" size="23" /></p>
<p><label for="myemail">Email:</label><input type="text" name="email" maxlength="50" size="23" /></p>
<p><label for="Submit" id="buttons"></label><input type="submit" name="Submit" value="Submit" /><input type="reset" name="Reset" /></p>
</fieldset>
</form>


I hate being stuck for too long on one thing, especially when I have successfully used the isset function before in exactly the same way and it worked.

Kind regards,

LC.

Fou-Lu
07-26-2012, 08:58 PM
Input types that are texts even if left empty are submitted to the server. So therefore the $_POST['field'] is considered set and passes a check for an isset. To check empty, you would use the empty() function (which only takes one variable at a time).
My preference is to use both, as I can then tell which fields are empty, as well as containing an isset check. I typically avoid the use of $_SERVER checks when possible, so I'd do an isset check on each applicable field, then if they are all good I run my verification and validation steps on the input data (ie: is empty? Is shorter than? is greater than? contains? Etc).

So with what you have:


if (isset($_POST['user'], $_POST['pass'], $_POST['email']))
{
$aErrors = array();
$user = trim($_POST['user']);
$pass = trim($_POST['pass']);
$email = trime($_POST['email']);

if (empty($user))
{
$aErrors[] = 'Username cannot be empty.';
}
if (empty($pass))
{
$aErrors[] = 'Password cannot be empty.';
}
if (empty($email))
{
$aErrors[] = 'Email cannot be empty.';
}

if (empty($aErrors))
{
// all is fine.
}
else
{
// or pretty it up with some ul's or whatever
printf('The following errors have occurred: %s' . PHP_EOL, implode("\n", $aErrors));
header("refresh:5, url=index.php");
exit(0);
}
}
else
{
echo "There was an issue with the server <br>";
echo "You will be redirected shortly.";
header("refresh:5, url=index.php");
exit(0);
}

For example.

Fields like checkboxes and radios are only successful if one or more was actually checked. I don't have a link handy here, but if you google for the spec for successful form fields, you should get the definition of what is successful and in what scenarios from the w3.

LearningCoder
07-27-2012, 02:09 PM
Ah thank you for taking the time to explain and provide code. I can understand everything apart from the printf statement. What does PHP_EOL do? I've read about implode before but I'm going to have another read now.

Just ordered a new Sams Teach yourself php, mysql and apache and judging by the contents I believe it could be an ideal read for myself. Reading online from a website just doesn't really help my understanding.

Kind regards Fou,

LC.

Fou-Lu
07-27-2012, 09:21 PM
PHP_EOL is a constant matching the system's linefeed character. It is never necessary in PHP's output source, but often necessary in file handling to provide a linefeed, and I just stick with the system one provided instead.
I should have used it in the implode as well, but meh.

LearningCoder
07-28-2012, 09:43 AM
I read up a little bit about PHP_EOL and just wanted to ask -- Is it meant to do the same as a HTML <br> tag and print the statements on separate lines but in a "cross-platform way"?

Can I also say, that when I have tried to use "\n" in the past to start a new line, it doesn't actually display anything on a newline. I believe I have mis-understood a little about what "\n" is meant to do. I've only ever tried to use it once and it didn't work as I thought.

For instance, the code that you gave me above prints to the screen like:

The following errors have occurred: Password cannot be empty. Email cannot be empty.

I read up on implode so the first parameter is what to separate the joined array string with and then the array to join to one big string. I presume the "\n" is meant to put each error on it's very own line?

I hope someone can explain it in a very simple way to help me truly understand what it does. I've read php.net and a few other 'example' sites.

You stated it's not necessary in PHP's output source? From that, I had in my mind that it isn't meant to display data on a new line (html br tag) but more likely to be doing stuff behind the scenes?

I have used your code Lou and it works fantastic, thank you. Every error is caught but before I move on, I really want to understand what that printf statement is doing as I hate not understanding the code I have.

Kind regards,

LC.

Fou-Lu
07-28-2012, 02:48 PM
\n isn't the same as <br />. \n will still provide a linefeed, but HTML ignores whitespace. If you want to map \n to an html break, you can use nl2br() function to insert a <br /> before the \n.
For an output formatter, simply sprintf it instead:


$out = sprintf('The following errors have occurred: %s' . PHP_EOL, implode(PHP_EOL, $aErrors));
print nl2br($out);

Or print and wrap the sprintf in nl2br directly.

Alternatively you can simply nl2br the results of the implode with PHP_EOL, or use <br /> as the delimiter. I don't assume that the output will ever be to a browser, so I always map it to HTML after the fact.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum