Posted by Michael Martinek on 11/26/07 13:20

>
> This is the statement that must be in the source file to connect to a
> database:
>
> mysql_connect(localhost,$username,$password);
>
> with $username and $password defined elsewhere in the source file.
> This seems scary to me!

Indeed. And I've been working on solutions to that problem lately.
Typically, the reason you want to worry about this is an attack
gaining access to the server who can just obtain your passwords and
such from scripts to access databases. It doesn't usually have
anything to do with someone downloading the PHP source from a
misconfigured server, as you'd be a fool to have your database server
open to the internet. I'll cover more on my idea in a moment.



> How to properly defend against an injection attack? Wikipedia has the
> following code as for how to defend:
>
> $query_result = mysql_query
> (
> "select * from users where name = '"
> .
> mysql_real_escape_string($user_name, $dbh)
> .
> "'"
> );
>
> If this is all it takes to defend against the attacks why is such a
> big deal made about them? Is there something more that you need to
> defend against?

A lot of outsourced work and "out of college" developers don't take
security into consideration. In some cases, it's companies going to
pre-packages solutions or source snippets from the web which are just
examples to do something; which never get fixed.

You can even defend against attacks by using addslashes(). Situations
where addslashes() and mysql_real_escape_string() is useless is
something like this:

$nAccountID = /*some user input..*/ "0; DROP users;"
$nAccountID = mysql_real_escape_string($nAccountID);
$sSQL = "SELECT * from users where account_id = {$nAccountID}";

mysql_real_escape_string will not change $nAccountID from the value of
"0; DROP users;".. there's nothing to be escaped in the string. The
proper way to protect against SQL injection is usually a combination
of sprintf() or intval(), and mysql_real_escape_string(). For example:

$nAccountID = intval("0; DROP users;"); // comes out 0

$sSQL = sprintf('SELECT * FROM users WHERE account_id = %u',
$nAccountID)

Note here we don't need mysql_real_escape_string() because sprintf()
is going to error if $nAccountID is not a number.

$sSQL = sprintf('SELECT * FROM users WHERE user_name = "%s"',
mysql_real_escape_string($sUsername));

This will escape the string to remove SQL escape attempts.

Now, mysql_query() has a protection built-in.. it will only execute
one query. So if $sUsername included multiple statements, only the
first would be executed.. which would be enough to complete the
statement you started, then the second statement would be the harmful
one.


> Also one more question on how to keep track of people who are
> submitting information on a website. How to set a time limit to how
> often people can submit information? This is easy to do on the client
> side, just disable the button for a set amount of time, but if they
> went hunting through my html and found the php script they could
> easily whip up a program to POST information willy nilly as fast as
> they wanted.

JavaScript methods are like car keys. They keep honest people honest.
Anyone who really wants to, or knows how can just bypass it. That's
where you implement something server side with a database. This would
usually be done by creating a table in a database to track recent
submissions.. usually tracking IP or account ID and submission
timestamp.

When you get a submission request, check

sprintf('SELECT COUNT(*) FROM thetable WHERE ip = %u AND submit_stamp
= >= %u',
ip2long($_REQUEST['REMOTE_ADDR']), time() - (60 * 5))

To check for how many submissions were made in the last five minutes.


In securing a site and protecting your databases.. it comes down to,
NEVER trust user input! Even if you're writing an internal tool where
employees of your own company will be using them.. DON'T pass
unescaped data to the database! Even if they aren't trying to cause
problems, eventually something is going to happen. Also, don't give
more rights to a script than it needs. I have a lot of user accounts
in my databases. When a page only needs to execute a function or issue
a SELECT, there's no reason to give it UPDATE, INSERT, or DROP
privileges. In some situations, I don't even give it SELECT.. I only
give it EXECUTE, and write a procedure which issues the SELECT, which
the PHP page will do a "CALL getAccountName(%u)" where %u is the
provided account ID on and process the results. The procedure in MySQL
is written with minimal security needed. For example, if my user on
the PHP page only has rights for a EXECUTE but they need to SELECT or
UPDATE data.. you need to define your procedure or function in MySQL
with SQL SECURITY DEFINER, and provide a DEFINER which has rights to
update or edit the table in question.

Really, I could go on all day about this.. security has always been a
big thing for me, having turned ethical hacker many years ago.

As for the solution I'm working on, it'll work with Apache and PHP.
It's an application written in PHP, compiled with Roadsend PHP
compiler to an application. It gets the MD5 checksum of scripts and
all theirs parents, up to the init() process. It makes sure that
nothing has been edited or tampered with. To get a DB connection,
you'd provide the DB hostname and user name; and the script itself
would return the password. The password is actually generated off the
hostname, user name and script MD5.. so not even the local developers
would know it. If the page trying to get the password for a DB
connection is modified.. say, to add a "echo $sPassword;" then it
wouldn't pass the sanity check and would never get a password back.
The way this would work, is that a PHP script is created that contains
a wrapper to this application.. that's the primary script we're making
sure hasn't been changed. It returns a link to the database. This
allows changes to other scripts which call the wrapper, without making
an administrator re-run the password generation tools to sync with
MySQL every time scripts are changed on a web site.

But whee.. security. Fun stuff.


Regards,
Michael Martinek

[Back to original message]