Home » Database » DB » Bug #206

[2003-11-06 17:09 UTC] hnm at intigo dot ch

Description:
------------
The example below works ok with mysql but not with oci8
after some debugging, found that OCIParse is called with a wrong statement:
query = SELECT ! FROM ! WHERE ! = :bind0
where the query for mysql is:
query = SELECT user,pass from users where user = 'demo'
is the error in Auth.php or in oci itself
(connection to oracle is OK)
Thanks

Reproduce code:
---------------
// set params dns, table, usernamecol, passwordcol
$a = new Auth("DB", $params );
$a->start();
if ($a->getAuth()) {
if($_GET[act] == "logout") {
$a->logout();
.... // logout message
} else {
.... // welcome
}
} else {
... // not authenticated
}

Expected result:
----------------
Wrong login data! instead of

Welcome user demo!

[2003-11-13 16:33 UTC] marcel at sipoc dot de

the problem is that prepare() in oci98.php seems not to work for SELECT Statements. Check http://pear.php.net/bugs/bug.php?id=91 also.

This is a quick dirty patch:
Auth/Container/DB.php (Line 247)

// prepare seems not to work for select statements ... build the query teh old fashion way !
//$res = $this->db->getRow($query, $query_params, DB_FETCHMODE_ASSOC);
$query = "SELECT ".$sql_from." FROM ".$this->options['table']." WHERE ".$this->options['usernamecol']." = '".$username."'";

[2003-11-14 14:17 UTC] hnm at intigo dot ch

oci8 is working. the real problem is the use of prepare/execute combination. You cannot bind a table or attribute name (as done in Auth/Container/DB.php)
the proposed fix to bug 91 is half the solution. Auth/Container
the pace holder ! value is inserted 'as is' (see DB/common.php) should be interpreted within prepare (this
requires passing query and parameters to prepare).
Other problem: uppercase & lowercase : getRow returns a oci8
result with attribute in uppercase (Oracle convention for table & attribute name).
So :$a = new Auth("DB", $params);
with
$params = array(
"dsn" => "oci8://scott:tiger@TMIC9",
"table" => "USERS",
"usernamecol" => "name",
"passwordcol" => "pass"
);
will never work, while
$params = array(
"dsn" => "oci8://scott:tiger@TMIC9",
"table" => "USERS",
"usernamecol" => "NAME",
"passwordcol" => "PASS"
);
is ok.
patch done in Container/DB.php:
// $query = "SELECT ! FROM ! WHERE ! = ?";
$query = "SELECT " . $sql_from . " FROM " . $this->options['table'] . " WHERE " . $this->options['usernamecol'] . " = ?";
$query_params = array(
//HNM $sql_from,
//HNM $this->options['table'],
//HNM $this->options['usernamecol'],
$username
);marcel at sipoc dot de
on the other hand the proposed solution from
marcel@sipoc.de
$query = "SELECT ".$sql_from." FROM ".$this->options['table']."
WHERE ".$this->options['usernamecol']." = '".$username."'";
is enough.
(immediate sql for auth is enough - we don't need a complete
prepare / execute stuff in this context).

The bugg 206 is for me closed. however a correct solution
for prepare/execute across all packages for a proper work
with oci8 must be found.