History | Log In     View a printable version of the current page.  
Issue Details (XML | Word | Printable)

Key: ZF-1821
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Ralph Schindler
Reporter: Darby Felton
Votes: 5
Watchers: 7
Operations

If you were logged in you would be able to see more operations.
Google issue summary
Zend Framework

Zend_Auth_Adapter_DbTable SQL incompatible with MS SQL Server

Created: 07/Aug/07 07:21 AM   Updated: 23/Jan/08 12:09 PM
Component/s: Zend_Auth
Affects Version/s: 1.0.1
Fix Version/s: None

Time Tracking:
Not Specified

File Attachments: 1. File DBTable-portable-sqll.diff (2 kb)


Tags:
Participants: Darby Felton, Marc Holzwarth, Ralph Schindler and Rob Allen


 Description  « Hide
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...



 All   Comments   Work Log   Change History   FishEye   Crucible      Sort Order: Ascending order - Click to sort in descending order
Rob Allen - 07/Aug/07 07:48 AM
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!


Marc Holzwarth - 08/Aug/07 02:56 AM
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

Ralph Schindler - 26/Dec/07 11:38 PM
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


Ralph Schindler - 27/Dec/07 06:09 PM
I implemented
(CASE WHEN "password" = 'aa46347de7c452' THEN 1 ELSE 0 END) AS zend_auth_credential_match

in r7278


Ralph Schindler - 23/Jan/08 12:09 PM
Resolved in r7598