Re: creating view with user variable

On 1/26/2017 4:55 AM, David wrote:
[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
>> ==================

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