Re: creating view with user variable

[Quoted] [Quoted] On Monday, January 23, 2017 at 10:32:52 PM UTC, Jerry Stuckle wrote:
> On 1/23/2017 12:50 PM, David wrote:
> > Hi guys,
> >
> > 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}
> >
> >
> > Then in the user accesible pages, it would be called by
> >
> > SELECT * FROM test WHERE (but here is where I get stuck)
> >
> > I have found a few articles knocking around which suggest creating a function and then calling the function by the user instead of calling the view , but all examples I tried this always through up errors.
> >
> > Would be most grateful if someone could point me in the right direction.
> >
> > Dave.
> >
>
> Dave,
>
> You would put the WHERE clause in your SELECT statement, not in the
> CREATE VIEW, i.e.
>
> CREATE VIEW test As
> SELECT * FROM userAccount
> LEFT JOIN userDetails On userAccount.ID = userDetails.UID;
>
> SELECT * FROM test WHERE ID = {variable}
>
> However, generally it's better to specify the individual columns instead
> of *, and is required if you have duplicate column IDs.
>
> But I'm not sure what you're trying to accomplish here. You're JOINING
> to userDetails, but not selecting any columns from it. Which brings up
> the question - what are you REALLY trying to do?
>
>
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> jstucklex_at_attglobal.net
> ==================

[Quoted] Hi Jerry,

Thanks for your response on this. I know normally you would leave the where clause out of the view but I am trying to lock down our database as much as possible.

[Quoted] 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

Dave. Received on Tue Jan 24 2017 - 08:24:40 CET