title: Is this PHP/MySQL login script secure? UPDATED CODE
body: <p>Greetings,</p>
  
  <p>A site I designed was compromised today, working on damage control at the moment. Two user accounts, including the primary administrator, were accessed without authorization. Please take a look at the log-in script that was in use, any insight on security holes would be appreciated. I am not sure if this was an SQL injection or possibly breach on a computer that had been used to access this area in the past.</p>
  
  <p>Thanks</p>
  
  <pre><code>&lt;?php
      //Start session
      session_start();
      //Include DB config
      require_once('config.php');
  
      //Error message array
      $errmsg_arr = array();
      $errflag = false;
      //Connect to mysql server
      $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
      if(!$link) {
          die('Failed to connect to server: ' . mysql_error());
      }
      //Select database
      $db = mysql_select_db(DB_DATABASE);
      if(!$db) {
          die("Unable to select database");
      }
  
      //Function to sanitize values received from the form. Prevents SQL injection
      function clean($str) {
          $str = @trim($str);
          if(get_magic_quotes_gpc()) {
              $str = stripslashes($str);
          }
          return mysql_real_escape_string($str);
      }
      //Sanitize the POST values
      $login = clean($_POST['login']);
      $password = clean($_POST['password']);
  
      //Input Validations
      if($login == '') {
          $errmsg_arr[] = 'Login ID missing';
          $errflag = true;
      }
      if($password == '') {
          $errmsg_arr[] = 'Password missing';
          $errflag = true;
      }
  
      //If there are input validations, redirect back to the login form
      if($errflag) {
          $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
          session_write_close();
          header("location: http://somewhere.com");
          exit();
      }
  
      //Create query
      $qry="SELECT * FROM user_control WHERE username='$login' AND password='".md5($_POST['password'])."'";
      $result=mysql_query($qry);
  
      //Check whether the query was successful or not
      if($result) {
          if(mysql_num_rows($result) == 1) {
              //Login Successful
              session_regenerate_id();
              //Collect details about user and assign session details
              $member = mysql_fetch_assoc($result);
              $_SESSION['SESS_MEMBER_ID'] = $member['user_id'];
              $_SESSION['SESS_USERNAME'] = $member['username'];
              $_SESSION['SESS_FIRST_NAME'] = $member['name_f'];
              $_SESSION['SESS_LAST_NAME'] = $member['name_l'];
              $_SESSION['SESS_STATUS'] = $member['status'];
              $_SESSION['SESS_LEVEL'] = $member['level'];
              //Get Last Login
              $_SESSION['SESS_LAST_LOGIN'] = $member['lastLogin'];
              //Set Last Login info
              $qry = "UPDATE user_control SET lastLogin = DATE_ADD(NOW(), INTERVAL 1 HOUR) WHERE user_id = $member[user_id]";
              $login = mysql_query($qry) or die(mysql_error());
              session_write_close();
              if ($member['level'] != "3" || $member['status'] == "Suspended") {
                  header("location: http://somewhere.com");
              } else {
                  header("location: http://somewhere.com");
              }
              exit();
          }else {
              //Login failed
              header("location: http://somewhere.com");
              exit();
          }
      }else {
          die("Query failed");
      }
  ?&gt;
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Here is an updated version of the security script, please let me know what you think. Added a little SALT and a table to block both IP addresses (disabling the login form itself) and individual users after failing to authenticate four times. A Mayday email is also sent to an Admin and the user is notified that they have exceeded the limit.</p>
  
  <p>Any critiques would be appreciated!</p>
  
  <pre><code>&lt;?php
      //Start session
      session_start();
      //Include DB config
      include $_SERVER['DOCUMENT_ROOT'] . '/includes/pdo_conn.inc.php';
  
      //Error message array
      $errmsg_arr = array();
      $errflag = false;
  
      //Function to sanitize values received from the form. Prevents SQL injection
      function clean($str) {
          $str = @trim($str);
          if(get_magic_quotes_gpc()) {
              $str = stripslashes($str);
          }
          return $str;
      }
  
      //Define a SALT
      define('SALT', 'heylookitssuperman');
  
      //Sanitize the POST values
      $login = clean($_POST['login']);
      $password = clean($_POST['password']);
      //Encrypt password
      $encryptedPassword = md5(SALT . $password);
      //Input Validations
      //Obtain IP address and check for past failed attempts
      $ip_address = $_SERVER['REMOTE_ADDR'];
      $checkIPBan = $db-&gt;prepare("SELECT COUNT(*) FROM ip_ban WHERE ipAddr = ? OR login = ?");
      $checkIPBan-&gt;execute(array($ip_address, $login));
      $numAttempts = $checkIPBan-&gt;fetchColumn();
      //If there are 4 failed attempts, send back to login and temporarily ban IP address
      if ($numAttempts == 1) {
          $getTotalAttempts = $db-&gt;prepare("SELECT attempts FROM ip_ban WHERE ipAddr = ? OR login = ?");
          $getTotalAttempts-&gt;execute(array($ip_address, $login));
          $totalAttempts = $getTotalAttempts-&gt;fetch();
          $totalAttempts = $totalAttempts['attempts'];
          if ($totalAttempts &gt;= 4) {
              //Send Mayday SMS
              $to = "admin@somewhere.com";
              $subject = "Banned Account - $login";
              $mailheaders = 'From: noreply@somewhere.com' . "\r\n";
              $mailheaders .= 'Reply-To: noreply@somewhere.com' . "\r\n";
              $mailheaders .= 'MIME-Version: 1.0' . "\r\n";
              $mailheaders .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
              $msg = "&lt;p&gt;IP Address - " . $ip_address . ", Username - " . $login . "&lt;/p&gt;";
              mail($to, $subject, $msg, $mailheaders);
              $setAccountBan = $db-&gt;query("UPDATE ip_ban SET isBanned = 1 WHERE ipAddr = '$ip_address'");
              $setAccountBan-&gt;execute();
              $errmsg_arr[] = 'Too Many Login Attempts';
              $errflag = true;    
          }
      }
      if($login == '') {
          $errmsg_arr[] = 'Login ID missing';
          $errflag = true;
      }
      if($password == '') {
          $errmsg_arr[] = 'Password missing';
          $errflag = true;
      }
  
      //If there are input validations, redirect back to the login form
      if($errflag) {
          $_SESSION['ERRMSG_ARR'] = $errmsg_arr;
          session_write_close();
          header('Location: http://somewhere.com/login.php');
          exit();
      }
  
      //Query database
      $loginSQL = $db-&gt;prepare("SELECT password FROM user_control WHERE username = ?");
      $loginSQL-&gt;execute(array($login));
      $loginResult = $loginSQL-&gt;fetch();
  
      //Compare passwords
      if($loginResult['password'] == $encryptedPassword) {
          //Login Successful
          session_regenerate_id();
          //Collect details about user and assign session details
          $getMemDetails = $db-&gt;prepare("SELECT * FROM user_control WHERE username = ?");
          $getMemDetails-&gt;execute(array($login));
          $member = $getMemDetails-&gt;fetch();
          $_SESSION['SESS_MEMBER_ID'] = $member['user_id'];
          $_SESSION['SESS_USERNAME'] = $member['username'];
          $_SESSION['SESS_FIRST_NAME'] = $member['name_f'];
          $_SESSION['SESS_LAST_NAME'] = $member['name_l'];
          $_SESSION['SESS_STATUS'] = $member['status'];
          $_SESSION['SESS_LEVEL'] = $member['level'];
          //Get Last Login
          $_SESSION['SESS_LAST_LOGIN'] = $member['lastLogin'];
          //Set Last Login info
          $updateLog = $db-&gt;prepare("UPDATE user_control SET lastLogin = DATE_ADD(NOW(), INTERVAL 1 HOUR), ip_addr = ? WHERE user_id = ?");
          $updateLog-&gt;execute(array($ip_address, $member['user_id']));
          session_write_close();
          //If there are past failed log-in attempts, delete old entries
          if ($numAttempts &gt; 0) {
              //Past failed log-ins from this IP address. Delete old entries
              $deleteIPBan = $db-&gt;prepare("DELETE FROM ip_ban WHERE ipAddr = ?");
              $deleteIPBan-&gt;execute(array($ip_address));
          }
          if ($member['level'] != "3" || $member['status'] == "Suspended") {
              header("location: http://somewhere.com");
          } else {
              header('Location: http://somewhere.com');
          }
          exit();
      } else {
          //Login failed. Add IP address and other details to ban table
          if ($numAttempts &lt; 1) {
          //Add a new entry to IP Ban table
          $addBanEntry = $db-&gt;prepare("INSERT INTO ip_ban (ipAddr, login, attempts) VALUES (?,?,?)");
          $addBanEntry-&gt;execute(array($ip_address, $login, 1));
          } else {
              //increment Attempts count 
              $updateBanEntry = $db-&gt;prepare("UPDATE ip_ban SET ipAddr = ?, login = ?, attempts = attempts+1 WHERE ipAddr = ? OR login = ?");
              $updateBanEntry-&gt;execute(array($ip_address, $login, $ip_address, $login));
          }
          header('Location: http://somewhere.com/login.php');
          exit();
      }
  ?&gt;
  </code></pre>