Zend Framework

Zend_Auth_Adapter_DbTable SQL incompatible with MS SQL Server

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: 1.0.1
  • Fix Version/s: None
  • Component/s: Zend_Auth
  • Labels:
    None

Description

Original message from Rob Allen:

The SQL generated by Zend_Auth_Adapter_DbTable::authenticate() creates
the following SQL for MS SQL Server:

SELECT
        "users".*,
        "password" = 'aa46347de7c4529eb7a1ce163daaa197fd1f1a62'
                        AS zend_auth_credential_match
        FROM "users"
        WHERE
            ("username" = 'rob')

This doesn't work and creates the following error:

SQLSTATE[HY000]: General error: 10007 Incorrect syntax near the keyword
'AS'. [10007] (severity 5) [(null)]

I've attached the patch I've used to get it working. It removes
functionality though as you can now no longer tell the difference
between the user not being in the database and the password being wrong.
Personally I don't need that, so I haven't fixed it as the only portable
way I can think of requires another database call.

I'll leave it to an SQL expert to come up with a better solution that
works across all supported databases.

Regards,

Rob...

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Rob Allen added a comment -

I've upped the priority as this is a show-stopper bug for Zend_Auth on MS SQL Server at least.

It doesn't help that the Exception message that is raised doesn't actually tell you what the problem is, so you assume that it's a problem with your code when it isn't!

Show
Rob Allen added a comment - I've upped the priority as this is a show-stopper bug for Zend_Auth on MS SQL Server at least. It doesn't help that the Exception message that is raised doesn't actually tell you what the problem is, so you assume that it's a problem with your code when it isn't!
Hide
Permalink
Marc Holzwarth added a comment -

Using CASE expression for password test may correct this problem.

AFAIK, most database implementations of CASE expression are ANSI SQL-92 compliant.

All sql queries below works on MSSQL :

zend_auth_credential_match = (CASE WHEN "password" = 'aa46347de7c452' THEN 1 ELSE 0 END)
(CASE WHEN "password" = 'aa46347de7c452' THEN 1 ELSE 0 END) AS zend_auth_credential_match
(CASE WHEN "password" = 'aa46347de7c452' THEN 1 ELSE 0 END) zend_auth_credential_match
Show
Marc Holzwarth added a comment - Using CASE expression for password test may correct this problem. AFAIK, most database implementations of CASE expression are ANSI SQL-92 compliant. All sql queries below works on MSSQL : 
zend_auth_credential_match = (CASE WHEN "password" = 'aa46347de7c452' THEN 1 ELSE 0 END)
(CASE WHEN "password" = 'aa46347de7c452' THEN 1 ELSE 0 END) AS zend_auth_credential_match
(CASE WHEN "password" = 'aa46347de7c452' THEN 1 ELSE 0 END) zend_auth_credential_match
Hide
Permalink
Ralph Schindler added a comment -

Does anyone watching this issue have access to a MS-SQL server that they can test new adapters with? I do not have direct access to one, but would like to find a workable solution to this within the next week.

-ralph

Show
Ralph Schindler added a comment - Does anyone watching this issue have access to a MS-SQL server that they can test new adapters with? I do not have direct access to one, but would like to find a workable solution to this within the next week. -ralph
Hide
Permalink
Ralph Schindler added a comment -

I implemented 

(CASE WHEN "password" = 'aa46347de7c452' THEN 1 ELSE 0 END) AS zend_auth_credential_match

in r7278

Show
Ralph Schindler added a comment - I implemented 
(CASE WHEN "password" = 'aa46347de7c452' THEN 1 ELSE 0 END) AS zend_auth_credential_match
in r7278
Hide
Permalink
Ralph Schindler added a comment -

Resolved in r7598

Show
Ralph Schindler added a comment - Resolved in r7598
Hide
Permalink
Euller added a comment - - edited

This list states that the problem has been solved, but I continue having problems on accessing the database Sql Server using Zend_Auth. You have the implementation and Exception below.

I would like quoting that the fields on table are correct, I tested them using the database mysql.

Exception

The supplied parameters to Zend_Auth_Adapter_DbTable failed to produce a valid sql statement, please check table and column names for validity.

Implementation

public function login($login, $senha) {		
		$authAdapter = new Zend_Auth_Adapter_DbTable ( 
		Zend_Registry::get ( 'database' ), 'user', 'email', 'password', 'SHA1(?)' );
		$authAdapter->setIdentity ( '' . $login . '' )->setCredential ( '' . $senha . '' );
		$auth = self::getInstance ();
		$result = $auth->authenticate ( $authAdapter );
		if ($result->isValid ()) {
			$data = $authAdapter->getResultRowObject ( null, 'senha' );
			$auth->getStorage ()->write ( $data );
			$auth->getIdentity ();
			return true;
		} else {
			return false;
		}
	}
Show
Euller added a comment - - edited This list states that the problem has been solved, but I continue having problems on accessing the database Sql Server using Zend_Auth. You have the implementation and Exception below. I would like quoting that the fields on table are correct, I tested them using the database mysql. Exception The supplied parameters to Zend_Auth_Adapter_DbTable failed to produce a valid sql statement, please check table and column names for validity. Implementation 
public function login($login, $senha) {		
		$authAdapter = new Zend_Auth_Adapter_DbTable ( 
		Zend_Registry::get ( 'database' ), 'user', 'email', 'password', 'SHA1(?)' );
		$authAdapter->setIdentity ( '' . $login . '' )->setCredential ( '' . $senha . '' );
		$auth = self::getInstance ();
		$result = $auth->authenticate ( $authAdapter );
		if ($result->isValid ()) {
			$data = $authAdapter->getResultRowObject ( null, 'senha' );
			$auth->getStorage ()->write ( $data );
			$auth->getIdentity ();
			return true;
		} else {
			return false;
		}
	}
Hide
Permalink
Ralph Schindler added a comment -

Can you echo out the SQL statement that is being generated? You can do this by adding echo'ing the $dbSelect->__toString() from within the method Zend_Auth_Adapter_DbTable::_authenticateQuerySelect().

Additionally, attempt to var_dump the Exception $e.

-ralph

Show
Ralph Schindler added a comment - Can you echo out the SQL statement that is being generated? You can do this by adding echo'ing the $dbSelect->__toString() from within the method Zend_Auth_Adapter_DbTable::_authenticateQuerySelect(). Additionally, attempt to var_dump the Exception $e. -ralph

People

Vote (5)
Watch (8)

Dates

  • Created:
    Updated:
    Resolved:
Atlassian JIRA (v4.4.3#663-r165197) | Report a problem
Powered by a free Atlassian JIRA open source license for Zend Framework. Try JIRA - bug tracking software for your team.