How can I make this PHP snippet accessing SQL secure?

Question

Problem

I have the below snippet in a website that I'm building at the moment. I know it is not secure due to SQL injection etc. but what is the correct way to solve that? I see related questions asking the same thing but with MySQL, and for that there is PDO, but PDO_OCI is experimental so I don't want to use it.

What other options do I have? Do I just create a function to strip certain characters and wrap that around the $_POST, something like str_replace(';', '', $_POST['username']);?

The below snippet is the only part of the website that actually takes user input, $_POST, in a query so I just need to make sure that I get the below correct.

Code

<?php

if (!empty($_POST)) {

  $stid = oci_parse($conn, "SELECT CustomerNo FROM Customers WHERE Username = '" . $_POST['username'] . "' AND Password = '" . $_POST['password'] . "'");
  oci_execute($stid);

  $row = oci_fetch_array($stid, OCI_NUM);
  if (!empty($row['0'])) {

    session_start();
    $_SESSION['customer'] = $row['0'];
    $_SESSION['username'] = $_POST['username'];

  }

  oci_free_statement($stid);
  oci_close($conn);

}

?>

or you can use `oci_bind_by_name` to bind parameters if PDO is against your religion or something... — Milan Halada, May 21 '14 at 07:58
@user3636943 you can see it in Ic.'s answer, if you need details for how it is working read documentation. — Milan Halada, May 21 '14 at 08:04

lc. · Accepted Answer · 2014-05-21T08:04:21.450

A quick Google search for "php oci parameter" yielded the oci_bind_by_name function, which looks like it can be used to create parameterized queries (i.e. The Right Way™).

Extrapolating from the examples on that page, you would most likely change your code to something like (untested, but most likely correct):

<?php

if (!empty($_POST)) {

  $stid = oci_parse($conn, 'SELECT CustomerNo FROM Customers WHERE Username = :username AND Password = :password');

  oci_bind_by_name($stid, ':username', $_POST['username']);
  oci_bind_by_name($stid, ':password', $_POST['password']);

  oci_execute($stid);

  $row = oci_fetch_array($stid, OCI_NUM);
  if (!empty($row['0'])) {

    session_start();
    $_SESSION['customer'] = $row['0'];
    $_SESSION['username'] = $_POST['username'];

  }

  oci_free_statement($stid);
  oci_close($conn);

}

?>

This way your user input will never get mixed with the SQL statement. Oh, and I really hope that password is not in plain text like it would appear to be... :'-(

Thanks. I was just re-writing this looking at `oci_bind_by_name` and came up with pretty much the same thing but I still don't understand how this helps. `:username` could be an actual username, but what if `:password` is `password; DROP ...` would that then equal `AND Password = password; DROP ...`? — user3636943, May 21 '14 at 08:11
Because in parameterized queries the value of the parameter is not mixed in with the statement; it is treated strictly as data. Look at the "explanation" section of [this answer](http://stackoverflow.com/a/60496/44853) for more details. The basic concept is the same regardless of RDBMS, library, or implementation. — lc., May 21 '14 at 08:14

How can I make this PHP snippet accessing SQL secure?

Problem

Code

1 Answers1