Re: creating view with user variable

From: David <david.greenhall_at_gmail.com>
Date: Thu, 26 Jan 2017 01:55:08 -0800 (PST)
Message-ID: <8e1ebca0-059b-41e7-9346-d667bb473bb3_at_googlegroups.com>


[Quoted] On Tuesday, January 24, 2017 at 1:20:39 PM UTC, Jerry Stuckle wrote:
> On 1/24/2017 2:24 AM, David wrote:
> > 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?
> >>
> >
> > 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.
> >
> > 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.
> >
>
> Well, think about it. If it's based on a variable from the web script,
> then any value can be passed and any rows can be retrieved (even if it's
> only one row at a time). No matter how you do it, if the web site is
> compromised, all rows will be available. There is no way around it.
>
> A SP like Axel would work. So would having a script on the server and
> using RPC to fetch the data as a JSON string or similar. This will give
> you more control over the data (better filtering), but you still have
> the potential of someone accessing your data.
>
> But your real problem here is security practices. You must ensure your
> server is secure, and if it is hacked, no one can get at your data.
> Things like keeping user ids and passwords outside of the web server's
> document root will help. Other methods can help, also.
>
> But the bottom line is - if the data is available to the web server, it
> will be available to a hacker.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> jstucklex_at_attglobal.net
> ==================

[Quoted] Hi Guys,

[Quoted] Thanks so much for all your input, using stored procedures is exactly what i was looking for and its working well.

There is one small issue I was hoping someone could answer.

How would I allow the person to re-order the data. In our old system, they could choose the column header on the website to reshuffle the sql query, and it would send through something like:

ORDER BY $field $direction (where these two fields are dynamic)

Now I am using a stored procedure, I have tried adding the two fields to the IN variables and then using them at the end of the query. This throws an error when I try to create the procedure with:

ORDER BY FIELD DIRECTION
You have an error with you sql syntax.

I have tried changing the field names to something different, but no matter what I use it still throws up the error.

Dave. Received on Thu Jan 26 2017 - 10:55:08 CET

Original text of this message