On-Rev mySQL security issues? -- or use an LC stack for the DB?

[Kay C Lan]

On Mon, Nov 28, 2011 at 11:55 AM, Tim Selander <selander at tkf.att.ne.jp>wrote:

>
> Any thoughts on validation needs for lookups only? And on using a stack or
> text file for the data? (Hmmm... how does the LC server handle variables -
> limit on size?)
>
> Tim,

again I'm no expert here (on anything, LC, SQL or On-Rev) but I did manage
to mangle LC, SQL and On-Rev together which is why I love LC.

Just a thought, but it sounds like your data is relatively small, and
basically all text. Weekly or monthly additions would only amount to a
couple of KB. If you wanted to eliminate security issues have you thought
about creating a desktop app and just having it call 'home' for the latest
db updates?

Assuming that answer to that is, yes, you have thought of it and want to go
with a Web interface for all the usual reasons, then I fully agree with
Pierre, whilst a desktop app LC and flat text files can perform at the same
sort of speed as an SQL database with small data sets, once you move to
On-rev the size of those flat files must be much smaller. I think the size
of the data you are looking at, an SQL db is the way to go.

As for validation, my answer is, it all depends. For security reasons, if
you set up the account correctly, with SELECT privileges only, then SQL
Injection problems, as mentioned by Bjornke  should be minimized. So to me
the need for validation would be dependent on the complexity of the queries
you are expecting to encounter, or more likely, how simple do you wish to
make a complex process.

As an example I love the 'DISTINCT' command in SQL combined with Option
buttons. In the case you've given, if Mr Suzuki didn't appear in any
Radio/TV programs during 1999, then his name wouldn't appear in the Name
Option button. If you build the SQL queries by check boxes and Option
buttons where you've provided ALL the data, then validation is automatic
and there is no possible way for SQL Injection to occur.

See Sarah's excellent example of using an Option Buttons on a webpage:

http://www.troz.net/onrev/samples/timezones.irev

On the other hand, you can end up with Option Buttons with way too many
entries, and catering for all the possible queries can be a real headache,
which is why I guess most web db searches opt for the text box where you
can type in virtually anything.  In this case rudimentary validation is
probably required, like ensuring letters aren't entered into date fields
and numbers aren't entered into name fields - but even then you'll run into
trouble, what happens if '50 Cent' or '2Pac' appeared on a show?

One option I've tried, that is a little clunky, is to use a text field and
to search letter for letter as they are entered and to display the number
of records found, but not display the actual records until the number is
less than 20 (abitrary). This can be used in combination with Option
Buttons for 'Year' and 'Show Name' for instance. For valid users they
quickly realise they are on the wrong/right track and enter other data. For
nefarious users they can't waste bandwidth or copy all your data by
displaying the entire db. The clunkiness I've found is that the first
letter always takes a long while for on-rev to respond, after that, the
response time is very respectable.

Hope that helps.