Re: creating view with user variable

From: Jerry Stuckle <jstucklex_at_attglobal.net>
Date: Thu, 26 Jan 2017 15:48:11 -0500
Message-ID: <o6dn6t$dq1$1_at_jstuckle.eternal-september.org>

On 1/26/2017 10:41 AM, David wrote:

> 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 :/
> 
> 
[Quoted] > 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.
> 

Dave,

[Quoted] Yes, you can use a prepared statement for that. Much less efficient, though and I tend to stay away from it. When doing things like this, I generally use RPCs - much more efficient and faster.

But you can't do it with the SQL you were trying.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================

Received on Thu Jan 26 2017 - 21:48:11 CET