MySQL: PHP or direct access?

Mark Waddingham 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?  
> I
> 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 
> be
> done with a direct MySQL connection using ssl. Also when using a web 
> service
> (LC server, PHP...) to access a MySQL DB over https what are the
> vulnerabilities?

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=" & 
field "Input"
   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 
the field:
   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 
AND is=2

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 
injection attacks.

Mark.

-- 
Mark Waddingham ~ mark at livecode.com ~ http://www.livecode.com/
LiveCode: Everyone can create apps




More information about the use-livecode mailing list