MySQL: PHP or direct access?

[Richard Gaskin]

Mark Waddingham

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

If it helps for those who may not know SQL as well as they know 
LiveCode, imagine a client that sends any data entered into a field to 
this LiveCode Server script:

    do $_GET

At that point you've allowed anyone using your client to execute 
anything LiveCode can do.

This is true with any interpreter, whether SQL, Python, Perl, Ruby, PHP, 
or anything else.

The benefit of a general-purpose language like PHP or LiveCode over a 
storage-specific language like SQL is that it's easier to sanitize 
inputs, making you safe from Little Bobby Tables:

<https://xkcd.com/327/>

-- 
  Richard Gaskin
  Fourth World Systems
  Software Design and Development for the Desktop, Mobile, and the Web
  ____________________________________________________________________
  Ambassador at FourthWorld.com                http://www.FourthWorld.com