mysql_real_escape_string

[richart]

Good morning,

 

I am trying to implement a simple sanitization of data before inserting in my database and am having a little trouble due to the fact that I am using a third party script that is accessing posted variables in a way that is unfamiliar to me... here's the data. The problem area is red. The form simply hangs up when submitted.  I have used this method in the past, but not with an object operator.

 

 // insert into database

 $servername = "localhost";

 $username = "psychtoo_richart";

 $password = "mypassword";

 $dbname = "mydatabase";  

 

 

 $fname = mysql_real_escape_string($easyForm->field['fname']['value']);

 $lname = $easyForm->field['lname']['value'];

 $title = $easyForm->field['title']['value'];

 $agency = $easyForm->field['agency']['value'];

 $telephone = $easyForm->field['telephone']['value'];

 $interest = $easyForm->field['interest']['value'];

 $email = $easyForm->field['email']['value'];

 

 // Create connection

 $conn = new mysqli($servername, $username, $password, $dbname);

 // Check connection

 if ($conn->connect_error) {

 die("Connection failed: " . $conn->connect_error);

 } 

 // sql statement

 $sql= "INSERT INTO registration (registration_date, fname, lname, title, agency, phone, interest, email)

 VALUES (NOW(), '$fname', '$lname', '$title', '$agency', '$telephone', '$interest2', '$email' )";

 

 $conn->query($sql);

 

 $conn->close();

 

[Ch0cu3r]

Using real_escape_string is not recommended to use. Your code uses mysqli so use prepared statements to insert your data into the table.

 

Note you cannot use the mysql_* and mysqli_* functions together.

[richart]

Thanks for the response.  I did some reading on prepared statements and came up with the following code.  Is there anything else you would add to prevent sql injection?

 

// database connection

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

 

// new data

 

  $fname = $easyForm->field['fname']['value'];

  $lname = $easyForm->field['lname']['value'];

  $title = $easyForm->field['title']['value'];

  $agency = $easyForm->field['agency']['value'];

  $telephone = $easyForm->field['telephone']['value'];

  $interest = $easyForm->field['interest']['value'];

  $email = $easyForm->field['email']['value'];

 

// query

 

$sql = "INSERT INTO registration (registration_date, fname, lname, title, agency, telephone, interest, email) 

            VALUES (NOW(),:fname,:lname,:title,:agency,:telephone,:interest,:email)";

 

$q = $conn->prepare($sql);

$q->execute(array(

                  ':fname'=>$fname,

                  ':lname'=>$lname,

                  ':title'=>$title,

                  ':agency'=>$agency,

                  ':telephone'=>$telephone,

                  ':interest'=>$interest,

                  ':email'=>$email

));

[Ch0cu3r]

Next step is to implement data validation, such as making sure the user did enter their first and last name. Their telephone number is a valid number/formatted correctly. Their email address is valid etc. If their data does not validate then you should not insert it in the database. 

 

You can use filter_var / filter_input for doing these validation checks.

[mikosiko]

 

Thanks for the response. I did some reading on prepared statements and came up with the following code. Is there anything else you would add to prevent sql injection?

 

.... ;

Yes... Be sure to use real prepared sentences instead of emulated ones (normally the default).

You must setup PDO::ATTR_EMULATE_PREPARES to FALSE

Read carefully about prepared sentences Y the reasons behind this setup here http://php.net/manual/en/pdo.prepare.php

 

In the users contributed note #7 is a good explanation of why

[richart]

Thanks!  I will read up on this.

[Jacques1]

While you fix your code, you also need to remove brainfarts like this one:

die("Connection failed: " . $conn->connect_error);

This prints internal database errors directly on the screen for everybody to see, which is obviously not the best idea. Not only are cryptic error messages very irritating for legitimate users. It also helps attackers gain detailed information about your system.

 

Internal error messages are for the developers (you), not the users. They belong into a log file. And the funny thing is: That's exactly what PHP does if you configure your php.ini appropriately. So no need for any weird die() statements.