CODEWITHSUNDEEP
phpmysqlsqlprepared-statementsql-injection
RaGe10940
RaGe10940

Reputation: 667

Understanding Prepared Statements - PHP

I am working on a school project for the finacial aid office at a university. The project is in production and have most of it done apart from a few little tweaks here and there. My main concern over the winter break (now) is security and preventing any breaches to the best of my abilities. People have told me to steer into Prepared Statements. I understand them to a good extent except for inserting data.

I have two forms : a login in form and student login form. The student login form enters why a student is coming to the office. that form is then submitted and that data is later retrieved by a table that shows counselors what students are waiting to be seen.

My problem is though each student who walks into the financial aid office has his or her own unique problem (most of the time) so now what confuses me is :

Do I need to think ahead and pre-make the insert queries or is there a way for there to be a "dynamic" query because there is a student comments box and for that it will be totally unique so how will I be able to create a query for that? 

<?php
define('DB_Name', 'dbtest');
define('DB_User', 'root');
define('DB_Password', 'testdbpass');
define('DB_Host', 'localhost');

$link = mysql_connect(DB_Host, DB_User, DB_Password);

if (!$link) {
  die ('Could Not Connect: ' . mysql_error ());
}

$db_selected = mysql_select_db(DB_Name, $link);

if (!db_selected) {
  die('Can Not Use ' . DB_name . ': ' . mysql_error());
}

$value1 = $_POST ['anum'];
$value2 = $_POST ['first'];
$value3 = $_POST ['last'];
$value4 = $_POST ['why'];
$value5 = $_POST ['comments'];

$sql = "INSERT INTO `dbfinaid` (anum, first, last, why, comments) VALUES ('$value1', '$value2', '$value3', '$value4', '$value5')";

if (!mysql_query($sql)) {
  die('Error : ' . mysql_error());
}

mysql_close();

and as I have been told doing it that way leaves me prone to SQL-Injections.

Any help will be very much appreciated. Thank you.

Upvotes: 6

Views: 2029

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562260

Building on the answer from @maček, here's an alternative way of doing the same thing. I find this easier:

$dbh = new PDO('mysql:host=localhost;dbname=dbtest', $user, $pass);

try {
  $query = $dbh->prepare("INSERT INTO `dbfinaid` (anum, first, last, why, comments)
    VALUES (:anum, :first, :last, :why, :comments)");

  $params = array_intersect_key($_POST, array_flip(array('anum', 'first', 'last', 'why', 'comments')));
  $query->execute($params);
}
catch (PDOException $e) {
  error_log($e->getMessage());
  die("An error occurred, contact the site administrator.");
}

I prefer to output the SQL error to a log, and show a different error to the user that doesn't confuse them with code details.

Upvotes: 5

maček
maček

Reputation: 77778

Once you read up on PHP's PDO you can rewrite your code like this

$dbh = new PDO('mysql:host=localhost;dbname=dbtest', $user, $pass);

try {
  $query = $dbh->prepare("INSERT INTO `dbfinaid` (anum, first, last, why, comments) VALUES (:anum, :first, :last, :why, :comments)");

  $query->bindParam(':anum',     $_POST['anum'],     PDO::PARAM_INT);
  $query->bindParam(':first',    $_POST['first'],    PDO::PARAM_STR);
  $query->bindParam(':last',     $_POST['last'],     PDO::PARAM_STR);
  $query->bindParam(':why',      $_POST['why'],      PDO::PARAM_STR);
  $query->bindParam(':comments', $_POST['comments'], PDO::PARAM_STR);

  $query->execute();
}
catch (PDOException $e) {
  die("error occured:" . $e->getMessage());
}

Upvotes: 6

Related Questions