I have always been slightly paranoid about sql injections. To that
end, I am following certain practices, such as:

1) all user input (received from forms, URLs or even file names) is
always quoted with $dbh->quote() function. In ADDITION to this,
parameters that should be, say, numeric, are so asserted.

2) All stored user passwords are encrypted with mysql PASSWORD()
function, not stored in plain text.

3) All read only access (SELECTs) is done with a database handle that
has only SELECT grant (a read only handle).

Idea #3 is mine and I really like it, this means that if someone could
subvert some SELECT statement, they could not alter a database
in any way. (inject a "DROP TABLE users" type of statement)

Due to my general paranoia, I would like to ask if there are any good
practices that I have not listed and should follow. I am talking
about SQL related stuff, not ideas like "do not pass user parameters
into popen()".

i

On 2011-02-12 04:27, Ignoramus20766 wrote:
[...]
Use prepared statements.


/Lennart

On 02/12/2011 04:27 AM, Ignoramus20766 wrote:
Item 3 is very good, IMHO an absolute necessity. If you have any pages
which allow updates, inserts or deletes to existing data, I would go a
step farther and use only MySQL stored procedures for those. Then you
can give your user EXECUTE privileges on those objects in addition to
the SELECT privilege. Direct updates or deletions of data in tables by
that user would still be disallowed, though. It works because the SP
runs with privileges of the DEFINER, usually a user having update,
delete and insert privileges -- although you can choose to have it run
with INVOKER privileges if you so desire.

In a stored procedure, you can do all sorts of things to enhance
security which would be very risky if you allowed the user to update any
tables directly.

On 2011-02-12, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:
Lennart, this is a great idea. I already started doing that. I have
quite a bit of legacy code, where I do not do it, but I use quoting
religiously. I will definitely use prepared statements from now on.

i

I never send database IDs to the client, but I hash them on the server
and send the hash instead. I use a different list of hashes for each
entity, and the hashes and IDs are stored in the session.

When I present a dropdown, for instance, the user can only select the
choices presented to him and altering the underlying submitted value is
pointless, because there is no stored ID connected with it.

This greatly reduces the danger of seeing or even abusing other user's data.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/