Re: creating view with user variable

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

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
==================