CODEWITHSUNDEEP
mysqlsqldatabase
arctek
arctek

Reputation: 219

MySQL Stored Procedure - Delete Ignoring 'WHERE' Clause

We have a stored procedure "GetToken", that is called when users log in to our site. This stored procedure generates a GUID (returning it to the client) and inserts an entry in to our "TokenIndex" table. The GUID is only valid for an extended period of time, so each time the user logs in we generate a new GUID and delete all prior "TokenIndex" entries for that particular user. The issue we're coming across (and it's driving us crazy) is that the the delete statement is deleting all entries inside the "TokenIndex" table, as if it's ignoring the WHERE LoginID = loginId clause inside the DELETE FROM TokenIndex statement. See below for our stored procedure:

CREATE DEFINER=`arcanatekauth`@`%` PROCEDURE `GetToken`(    
   IN _username NVARCHAR(100),   
   IN _password TINYBLOB   
)       
BEGIN
   declare guid CHAR(36);    
   declare clientId int;
   declare loginId int;
   declare clientConnectionString nvarchar(500);

   Select li.ClientID, li.ID INTO clientId, loginId
   FROM LoginIndex li
   WHERE li.UserName = _username and li.Password = _password;

   if(clientId > 0)
   then
   begin
      SET guid = UUID();            
      DELETE FROM TokenIndex
      WHERE LoginID = loginId;

      SELECT ci.ConnectionString INTO clientConnectionString
      FROM ClientIndex ci
      WHERE ci.ID = clientId;

      INSERT INTO TokenIndex (Token, LoginID, ConnectionString, ExpirationDtTm)
      VALUES (guid, loginId, clientConnectionString, NOW() + INTERVAL 1 HOUR);
   end;
   end if;

   select guid;
END

Upvotes: 1

Views: 1877

Answers (1)

paxdiablo
paxdiablo

Reputation: 881563

I think your problem lies here:

WHERE LoginID = loginId

From the online docs:

Column, index, and stored routine names are not case sensitive on any platform, nor are column aliases. Trigger names are case sensitive, which differs from standard SQL.

In other words, that clause may be being treated the same as:

where 1 = 1

in that it will select all rows for deletion.

Now I'm not certain that's the case but it should be fairly easy to confirm.

Simply replace all occurrences of loginId (with that specific combination of characters) with xyzzyLoginId and see if that fixes it.

Upvotes: 7

Related Questions