Need help with some SQL

Question

<?php

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

$email = mysql_real_escape_string($_POST['email']);
$password = mysql_real_escape_string($_POST['password']);

mysql_query("INSERT INTO accounts (email, password) VALUES ('$email', '$password')");

}

?>


<h1>Create Account</h1>

<form action="" method="post">
<table cellspacing="3">

<tr>
 <td valign="top">E-mail address:</td>
 <td><input type="text" name="email" size="28"></td>
</tr>

<tr>
 <td valign="top">Password:</td>
 <td><input type="text" name="password" size="28"></td>
</tr>

<tr>
<td colspan="2" align="center">
  <input type="submit" value="Submit">
</td>
</tr>

</table>
</form>

How can I prevent people from just entering spaces and insert empty entries? :/ And do you see any other potential threats?

Thanks. /Newbie

score 2 · Answer 1 · answered May 04 '09 at 10:34

2

if(!empty($_POST['email']) && !empty($_POST['password'])){
  $email = trim($_POST['email']); // Remove trailing and leading spaces
  $password = trim($_POST['password']); 
  if( $email != "" && $password != ""){
    $email = mysql_real_escape_string($email);
    $password = mysql_real_escape_string($password);
    mysql_query("INSERT INTO accounts (email, password) VALUES ('$email', '$password')");
  } else {
    //DISPLAY ERROR MSG
  }
}

answered May 04 '09 at 10:34

takete.dk

2,705
1
19
12

1

Also, never store plaintext passwoprds in a database, you should md5() or sha1() them. – Pim Jager May 04 '09 at 11:30
Validate the e-mail address with a regular expression. Hash the password. – James Socol May 04 '09 at 14:47

schooner · Answer 2 · 2009-05-04T10:59:24.743

You should be doing validation on the fields prior to calling any SQL to prevent unwanted entries as well as SQL injection.

You might also want to impose some checks on password length/strength, regex on the email, etc.

In your code now someone could enter anything and everything.

Also, are you handling duplciate entries as well in the DB side with a unique email/username constraint?

EDIT - Looks like you may have SQL Injection covered already with mysql_real_escape_string

score 0 · Answer 3 · answered May 04 '09 at 10:31

0

You could trim those values and check for empty strings and then ask the user for a real email address. You can also use regex as part of some validation. Lastly, I am not sure how to achieve this using PHP but you can parameterize the SQL.

answered May 04 '09 at 10:31

uriDium

13,110
20
78
138

score 0 · Answer 4 · answered May 04 '09 at 10:38

You should think about usability and about security.

For usability: add javascript-validators which will show some tooltips for users when users write e-mail in wrong format and so on.

For security: you should validate data before executing insert-query, validators can be the same as js-validators (but 'smart' users can simply disable javascript in browser)

Jonathan · Answer 5 · 2009-05-04T10:48:11.900

as schooner said, you should validate the fields prior to call the sql query. I think it's a good practice to keep a file with common functions used to validate fields, so you can reuse this functions many times in your code. This validations can be made using regular expresions. I left you an example.

function checkEmail($email) 
{
  if(preg_match("/^([a-zA-Z0-9])+([a-zA-Z0-9\._-])
               *@([a-zA-Z0-9_-])+([a-zA-Z0-9\._-]+)+$/",
               $email)){

    return true;
  }
  return false;
}

Also... if you need to validate a lot of input entries, maybe you will be interested in search for a open souce validation library in php.

The regexp for email addresses is very wrong. See http://stackoverflow.com/questions/201323/ — bortzmeyer, May 04 '09 at 14:46

Need help with some SQL

5 Answers5