PHP - Need to echo/print result of Oracle query in web page

Question

I've been searching for a couple of days and tried various forms of oci-fetch and not using it at all. What I'm trying to do is (code follows) get a submitted user ID (that we use in our institution) and obtain first and last name initals, add the last 4 digits of SSN and send as the reset password for the user.

I ran the first part of the sql in sql Developer, and satisfied myself that the desired outcome results.

Where I'm having a problem is taking the result of the sql and using it to update the ldap, then send in a web page. The holdup is obtaining the results of the sql via the php operations. Right now, I'm just trying to echo or print to make sure I'm getting the resultant password, but that's not happening.

Here's the code (adjusted for security):

<?php
$user = STRTOUPPER($_POST['uid']); //get input text
$conn = oci_connect("A_USER", "USER_PWORD>*", "DBSID");

if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

$query = "select UPPER(substr(a.per_lname,1,1)) || LOWER(substr(a.per_fname,1,1)) || a.per_sno from mccuser.Pers_info a where a.per_id = ('" . $user ."')";

$stid = oci_parse($conn, $query);
$success = oci_execute($stid);
//From this point, I've tried different approaches, including not even using oci_fetch.

oci_fetch_array($stid, OCI_RETURN_NULLS+OCI_ASSOC);
$newpw = $success;
echo $newpw;

?>

I'd appreciate any suggestions.

Thanks, dfonteno

Answer 1

The PHP documentation for the OCI extension is excellent and there are plenty of examples showing how to do what you want.

Your code is vulnerable to SQL Injection attack, so I've modified it to use binding.

<?php

$user = STRTOUPPER($_POST['uid']); //get input text
$conn = oci_connect('A_USER', 'USER_PWORD>*', 'DBSID', 'UTF8'); // explicitly use UTF-8

if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}

// field is aliased as 'PWD' since you want an associative array as result.
$query = 'select UPPER(substr(a.per_lname,1,1)) || LOWER(substr(a.per_fname,1,1)) || a.per_sno as pwd from mccuser.Pers_info a where a.per_id = (:usr)';
$stid = oci_parse($conn, $query);
// bind the user variable.
oci_bind_by_name($stid, ':usr', $user);
oci_execute($stid);
$result = oci_fetch_assoc($stid);
$newpw = $result['PWD']; // note the key is always uppercase by default.
echo $newpw;

That should do what you want. I do however agree with Mike_OBrien that this is a bad way to go. You replied:

The worst that can happen is someone can log into a student account and see their Grades or email someone, using the student's account.

I consider that incredibly bad. What if that account is used to email a bomb threat, or pornography? An innocent person could face criminal charges because of your lazy approach to application security.