Re: creating view with user variable

From: Axel Schwenke <axel.schwenke_at_gmx.de>
Date: Tue, 24 Jan 2017 09:45:55 +0100
Message-ID: <o6744v$d98$1_at_dont-email.me>


[Quoted] On 24.01.2017 08:24, David wrote:

>>> Can anyone tell me if its possible to create a view with a where clause linking to a variable which is passed by the user.
>>>
>>> Something like:
>>> CREATE VIEW test As
>>>  SELECT * FROM userAccount
>>>  LEFT JOIN userDetails On userAccount.ID = userDetails.UID
>>>  WHERE userAccount.ID = {variable}

>> I'm not sure what you're trying to accomplish here.

> The query I posted above was just a quick example hoping to show what I am trying to achieve. As it stands, if the where clause is held in the web pages which connect to the database, then there is a potential for all user accounts to be accessed if, in the unfortunate circumstances the website gets compromised and hacked - then someone could access the view and list all accounts.
> I am trying to lock it down, so regardless of whether the website is compromised or not, only records can come back from the view with a relevant userID (ie only 1 record - not all of them)
>
> I followed this article: http://stackoverflow.com/questions/2281890/can-i-create-view-with-parameter-in-mysql but could not get it to work; hence why I came here. There must be a way to lock down views to stop it bringing back all rows

That article is full of stupid ideas. It starts with using a user variable and doesn't end with encapsulating that variable in a stored function.

The way to go would be the use of a stored procedure. You just put your JOIN query in the procedure and give the user id as an IN parameter to that procedure. Calling the procedure will return the result set of the query.

From a security point of view this is still not very good. An attacker just needs to guess a user id and can still get the user details. And as we speak of security: in order to have any effect, make sure that the website MySQL user cannot access the userAccount and userDetails tables directly (only through the procedure). For that you have to set the DEFINER and SQL SECURITY attributes of the procedure accordingly. And you will need a lot more procedures before you can lock down those tables.

See http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx and https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html Received on Tue Jan 24 2017 - 09:45:55 CET

Original text of this message