mysql_real_escape_string preventing unsanitized fields with bad characters from being added to the database

Question

mysql_real_escape_string is preventing the unsanitized fields with bad characters from being added to the database. I don't want to have to specify all the fields on each form (since that's both cumbersome to do for each field and doesn't accommodate special characters which people may include or typos), but at the moment this code prevents anything from being inserted if any threatening characters are present in the unsanitized fields but still advances to the next page.

I'm also using jQuery validate on this page, but haven't been able to use that to prevent 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
   $user_name = clean($_POST['user_name']);
   $password = clean($_POST['password']);

//Create INSERT query
   $qry = "INSERT INTO customer_info(fname, lname, gender, zip, email, phone, terms, security_question, security_answer, participating_retailers, notify_new_items, notify_promotions, priority1, priority2, priority3, priority4, priority5, privacy, user_name, password) 
 VALUES('$_POST[fname]','$_POST[lname]','$_POST[gender]','$_POST[zip]','$_POST[email]','$_POST[phone]','$_POST[terms]','$_POST[security_question]','$_POST[security_answer]','$_POST[participating_retailers]','$_POST[notify_new_items]','$_POST[notify_promotions]','$_POST[priority1]','$_POST[priority2]','$_POST[priority3]','$_POST[priority4]','$_POST[priority5]','$_POST[privacy]','$user_name','$password')";
   $result = @mysql_query($qry);  


  $qry="SELECT * FROM customer_info WHERE user_name='$user_name' AND password='$password'";  
  $result=mysql_query($qry);            
  session_regenerate_id();
        $member = mysql_fetch_assoc($result);
        $_SESSION['SESS_USER_ID'] = $member['user_id'];
        $_SESSION['SESS_FIRST_NAME'] = $member['fname'];
        $_SESSION['SESS_LAST_NAME'] = $member['lname'];
        session_write_close();
        header("location: flatter-form.html");
        exit();

score 0 · Answer 1 · edited May 23 '17 at 12:12

0

mysql_query has been deprecated. PDO or mysqli both provide security against SQL injections. In addition to both having escaping functionality, PDO has the ability to also quote the string. Using prepared and parameterized queries makes it almost impossible for an attacker to inject SQL.

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array(':name' => $name));

foreach ($stmt as $row) {
    // do something with $row
}

Sample from: Prepared statements

Take a look at PDO vs. MySQLi.

edited May 23 '17 at 12:12

Community

1
1

answered Aug 23 '12 at 17:53

Kermit

33,827
13
85
121

This is completely the wrong way to do it. You're making PDO cry, you know, by using string concatenation. Please use placeholders. They are the **ONLY** way to be sure. – tadman Aug 23 '12 at 17:59
@tadman thanks for the feedback. I've modified my answer accordingly. – Kermit Aug 23 '12 at 18:12
@njk - Thank you :-) This is really helpful, and will definitely come in handy when I take the time to figure out and transition to mysqli :-) I'm going to leave the question as open though because I'm not able to switch frameworks just yet – Chaya Cooper Nov 22 '12 at 22:47

mysql_real_escape_string preventing unsanitized fields with bad characters from being added to the database

1 Answers1