Welcome to WebmasterWorld Guest from 54.162.49.89

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

validate email before DB submission

     
6:53 am on Jul 26, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2008
posts: 133
votes: 0


Hi,

I'm new to php and would like to check email address has the required charcters before submitting to DB, could anyone help understand where to insert the check please.

PHP enclosed:

<?php

if(isset($_POST['submitted'])) {

include('db/connect-mysql.php');

$email = $_POST['email'];

if($email == '') {

$err = true;
$msg = 'Please fill in your email address';

}
else
{

$sqlinsert = "INSERT INTO registered_users(email)VALUES('$email')";

if(!mysqli_query($dbcon, $sqlinsert)) {
die('Error inserting email into the database');

}
else
{

$ok = true;
$newrecord = 'Your subscription has been successful!';
}
}
}
?>

Many thanks in advance for any help.

geoffb
7:35 am on July 26, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:June 26, 2008
posts:178
votes: 0


Hi Geoff,

Here's a quick and dirty solution for you. ;) It will ensure your address follows a pretty standard format.


$error = 0;

// IF THE EMAIL FIELD ISN'T EMPTY
if ($email != "")
{
// CHECK THE EMAIL FIELD IS IN A TYPICAL EMAIL FORMAT USING REGEX
if(preg_match("/^[-\w.]+@([A-z0-9][-A-z0-9]+\.)+[A-z]{2,4}$/", $email))
{
// VALID EMAIL
}
else
{
// INVALID EMAIL
$error = 1;
}
}
else
{
// CAN'T BE EMPTY
$error = 1;
}

// THEN CHECK FOR YOUR ERROR BOOLEAN

if($error == 1)
echo "error"
else
mysql_query($query);


If you want to go one step futher, you could add a strlen to restrict the length of the email address.

I think the above method is good enough without having to do DNS lookups and such, so I hope it helps!

Tom.
7:42 am on July 26, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2008
posts: 133
votes: 0


Hi Tom,

Thanks for the code, just wondering how to get that into mine to work.

geoffb
9:05 pm on July 26, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Dec 13, 2009
posts:945
votes: 0


There's a neat little function build into PHP 5.2+

filter_var() [php.net]

It can be used to validate several different data types [php.net]

To tie it into your code:

if($email == '') {
$err = true;
$msg = 'Please fill in your email address';
} else if(filter_var($email, FILTER_VALIDATE_EMAIL)) {
$err = true;
$msg = 'Please enter a valid Email address';
} else {


Also, I can't stress this enough. With user inserted content, you should use a function such as mysql_real_escape_string() [php.net] to sanitize user input:


$sqlinsert = '
INSERT INTO
registered_users
SET
email = "' . mysql_real_escape_string($email) . '"
';


Not doing so leaves you open to SQL injection style attacks - where a malicious user can type their own SQL into your SQL command (e.g. ";DROP TABLE *; )
5:26 am on July 27, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2008
posts: 133
votes: 0


Hi Readie,

Undertaking your advice I made the alterations only now whatever I type in be it valid email or invalid it is returning with 'Please enter a valid Email address.

Probably something simple only cant figure it out if you could help.

PHP:

<?php

if(isset($_POST['submitted'])) {

include('db/connect-mysql.php');

$email = $_POST['email'];

if($email == '') {
$err = true;
$msg = 'Please fill in your email address';
} else if(filter_var($email, FILTER_VALIDATE_EMAIL)) {
$err = true;
$msg = 'Please enter a valid Email address';
} else {

$sqlinsert = 'INSERT INTO registered_users
SET
email = "' . mysql_real_escape_string($email) . '"';

if(!mysqli_query($dbcon, $sqlinsert)) {
die('Error inserting email into the database');

}
else
{

$ok = true;
$newrecord = 'Your subscription has been successful!';
}
}
}
?>


Thanks very much.

geoffb
3:44 pm on July 27, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Note that filter_var is only PHP 5 or greater - encountered "one of those sites" last week that refuses to budge and update. Good seeing you around Readie!
5:58 pm on July 27, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2008
posts: 133
votes: 0


Hi,

Long time since Ive heard from you Rocknbil, although not been on for a while.

Just had a look at the host and they run PHP 5.2.6, so it must be an error in the code.

Cheers
Geoff
8:58 pm on July 27, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 5+ Year Member

joined:Dec 13, 2009
posts:945
votes: 0


I failed :)

} else if(filter_var($email, FILTER_VALIDATE_EMAIL)) { 

Should be negative:

} else if(!filter_var($email, FILTER_VALIDATE_EMAIL)) { 

Sorry for that, was only a quick mock-up :)
6:02 am on July 28, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2008
posts: 133
votes: 0


Hi Readie,

Thanks very much.

Looks like my last issue is a mysql_real_scape_string issue.

After using a valid email address I get this warning:

Warning: mysql_real_escape_string() [<a href='function.mysql-real-escape-string'>function.mysql-real-escape-string</a>]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in ......

I am using an include to connect to the DB which has worked perfect and entered records into the DB, but since using the mysql_real_escape_string I get that warning.

I had a look around on the internet and some suggest that it is the placement of the DB connection so I tried moving it around but no joy.

My code so far:

<?php

if(isset($_POST['submitted'])) {

include('db/connect-mysql.php');

$email = $_POST['email'];

if($email == '') {
$err = true;
$msg = 'Please fill in your email address';
} else if(!filter_var($email, FILTER_VALIDATE_EMAIL)) {
$err = true;
$msg = 'Please enter a valid Email address';
} else {

$sqlinsert = 'INSERT INTO registered_users
SET
email = "' . mysql_real_escape_string($email) . '"';

if(!mysqli_query($dbcon, $sqlinsert)) {
die('Error inserting email into the database');

}
else
{

$ok = true;
$newrecord = 'Your subscription has been successful!';
}
}
}
?>

Thank you so much for the help so far.

geoffb
3:58 pm on July 28, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


Moved it around - to the right place? :-) That usually means you're using mysql_real_escape_string() before you've opened a mysql connection.

EDIT: Doh . . . caught this

mysqli_query

Use mysqli_real_escape_string()

docs [php.net]
5:54 pm on July 28, 2011 (gmt 0)

Junior Member

5+ Year Member

joined:Apr 20, 2008
posts: 133
votes: 0


Hi Rokinbil,

Almost there, just got a new error mesage though after submission:

Warning: mysqli_real_escape_string() expects exactly 2 parameters, 1 given in....

So what then did was edit the line:

email = "' . mysqli_real_escape_string($email) . '"';


with

email = "' . mysqli_real_escape_string($dbcon, $email) . '"';


It seemed to work fine, but I just want to make sure my subscription form doesnt leave me open to all sorts of attacks by not knowing PHP very well, or simple ways to secure as much as possible.

Thanks
geoffb