Re: creating view with user variable
Date: Thu, 26 Jan 2017 07:41:34 -0800 (PST)
Message-ID: <4511ee42-af59-46e7-bd48-e295d556ebee_at_googlegroups.com>
On Thursday, January 26, 2017 at 3:27:55 PM UTC, Jerry Stuckle wrote:
> On 1/26/2017 9:59 AM, David wrote:
> > 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
> >> ==================
> >
> > 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.
> >
>
> Dave,
>
> Sorry, SQL doesn't allow you to specify column names like this. It's
> not must MySQL - it's also true in every other RDBMS I'm familiar with.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> jstucklex_at_attglobal.net
> ==================
Hmmm, I was just about to update this post stating I have managed to do it when I notice you say its not possible :/
So in case anyone else comes across this issue, here is how I did it using CONCAT()... leaving out a lot of the SQL (as I dont think its relevant
DELIMITER $$
CREATE PROCEDURE fetchClientOrders(IN AccountID INT(11), IN col VARCHAR(15), IN dir VARCHAR(4))
BEGIN
SET _at_accountID = AccountID;
SET _at_col = col;
SET _at_dir = dir;
SET _at_sort = CONCAT('ORDER BY ',_at_col,' ',_at_dir,'');
SET _at_query = CONCAT('SELECT orderID,orderDetails,package,submissionDate FROM userOrders WHERE acc_id = ',_at_accountID,' ',_at_sort,'');
PREPARE stmt FROM _at_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$ Dave. Received on Thu Jan 26 2017 - 16:41:34 CET