MySQL: PHP or direct access?
mark at livecode.com
Thu Aug 13 13:49:58 EDT 2015
> Good question. Why is LC server instead of PHP not an option for you?
> know this has been talked about on the list before but I still don't
> have a
> solid handle on this. I'm still am not clear on how SQL injection can
> done with a direct MySQL connection using ssl. Also when using a web
> (LC server, PHP...) to access a MySQL DB over https what are the
SQL Injection attacks are independent on transport method because they
come about from a query being passed to the DB which is constructed with
'unsafe' input in a way that does not ensure that that input cannot
change the query itself.
Here 'unsafe' means something which has come from the client via some
sort of user input without being vetted or checked.
As a simple (rather stupid) example consider a client application which
has a field which expects a number, and an output field.
The user enters a number into the field and the client does a query to
fetch a piece of data mapped to that number in the DB. e.g.
query "SELECT content FROM data WHERE user=" & pUserId" && "AND id=" &
put the result into field "Output"
Here the input field is not being validated in anyway, nor is the value
being escaped. This means that I am then free (as a user of the client)
to put anything I want into that field. Imagine I put the following into
1 OR user=1 AND id=2
The query the client ends up sending to the DB is:
SELECT content FROM data WHERE user=<actualuserid> AND id=1 OR user=1
This query gives me access to data that I should not have - in
particular it allows me to fetch both the bit of data which is attached
to my user id (I'm assuming here that the client has worked out the user
id for the current user) *and* to a bit of data which is attached to
another user id.
The point is that failure to validate inputs which are built into
queries passed direct to a DB are a potential point where a malicious
individual could 'work out' how to access areas of the system to which
they should not have access.
If you use parameterized queries then you are largely insulated from
this as the input data is never concatenated into the query and passed
as parameters. This means the query cannot be altered by malicious users
by varying inputs in the client.
Fronting with a webservice doesn't necessarily stop injection attacks,
it just gives you an extra level of indirection at which validation,
checks and such can be done. i.e. If you don't validate the inputs to
the webservice, SQL injection can still happen if it concatenating those
inputs into a query string directly. It also encourages to think of the
database as just a datastore - typically you'd design your webservice
API at a level which is more appropriate to the client application, with
the work of building queries and constructing the results all done
'behind' a wall. This means the internal structure of the database and
the queries you send to it are far less easy to snoop upon from the
client, which means it would be harder to find possibilites for
Mark Waddingham ~ mark at livecode.com ~ http://www.livecode.com/
LiveCode: Everyone can create apps
More information about the Use-livecode