Welcome to WebmasterWorld Guest from 54.167.175.218

Forum Moderators: coopster & jatar k

validate email before DB submission

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

5+ Year Member



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 Jul 26, 2011 (gmt 0)

5+ Year Member



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 Jul 26, 2011 (gmt 0)

5+ Year Member



Hi Tom,

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

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

WebmasterWorld Senior Member 5+ Year Member



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 Jul 27, 2011 (gmt 0)

5+ Year Member



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 Jul 27, 2011 (gmt 0)

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



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 Jul 27, 2011 (gmt 0)

5+ Year Member



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 Jul 27, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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 Jul 28, 2011 (gmt 0)

5+ Year Member



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 Jul 28, 2011 (gmt 0)

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



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 Jul 28, 2011 (gmt 0)

5+ Year Member



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
 

Featured Threads

Hot Threads This Week

Hot Threads This Month