Re: creating view with user variable

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

You didn't give the entire SQL statement, so this is just a guess. It is not ORDER BY FIELD DIRECTION. If the field name is DIRECTION, it is just ORDER BY DIRECTION. Or, if you are ordering by FIELD and DIRECTION, it is ORDER BY FIELD, DIRECTION. And if you use reserved words (not recommended), a MySQL extension (also not recommended) is to use backticks around the name, i.e. ORDER BY `FIELD`, `DIRECTION`.

However, I normally do sorting like this with Javascript on the browser.  There is no real need to go back to the server just to sort; all of the information is right there.

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