Re: creating view with user variable

[Quoted] [Quoted] On Thursday, January 26, 2017 at 1:44:53 PM UTC, Jerry Stuckle wrote:
> On 1/26/2017 4:55 AM, David wrote:
> > 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
> >> ==================
> >
> > Hi Guys,
> >
> > 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.
> >
>
> P.S. When getting an error message, please show the entire statement and
> the error message you get. Otherwise we have to guess.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> jstucklex_at_attglobal.net
> ==================

[Quoted] Sorry Jerry, Yes I know I should have given the more information and the full error.

So my Procedure creation is like this:

CREATE PROCEDURE test(IN AccountID INT(15), IN FIELD VARCHAR(15), IN DIRECTION VARCHAR(4))

And basically I want "FIELD" and "DIRECTION" to be within the ORDER BY clause like this:

SELECT orderID,package,details FROM userOrders WHERE uid = AccountID ORDER BY FIELD DIRECTION;

so if the procedure is called like:

CALL test(1000,'orderID','ASC');

then the procedure will swap out FIELD and DIRECTION for orderID and ASC like:

SELECT orderID,package,details FROM userOrders WHERE uid = AccountID ORDER BY orderID ASC;

I hope this makes more sense.

I know using javascript is probably a lot more efficient, but at the moment I would like to keep the procedure doing the work.

Dave. Received on Thu Jan 26 2017 - 15:59:46 CET