Re: SQL and DB Connection Question (Interfacing with PHP) - Mailing list pgsql-novice

On Fri, Jan 21, 2005 at 01:40:33PM -0800, operationsengineer1@yahoo.com wrote:

> i've searched high and low on the net and have been
> unable to find a good source for how to handle php
> variables and constants in the sql string.  does
> anyone have a link to a good site?  i'm as much
> interested in the *why* as in the actual syntax.

The "why" typically concerns SQL injection.  For example, suppose
you have a variable $name that you obtained from a web form and you
build an SQL statement like this:

  $sql = "INSERT INTO person (name) VALUES ('$name')";

If $name contains "Smith" then the SQL statement will look like this:

  INSERT INTO person (name) VALUES ('Smith')

But if $name contains "O'Reilly" then the statement will look like this:

  INSERT INTO person (name) VALUES ('O'Reilly')

This will result in a syntax error because of the extra single quote
in "O'Reilly".  Now consider what will happen if $name contains
"evil'); DELETE FROM person; SELECT ('1":

  INSERT INTO person (name) VALUES ('evil'); DELETE FROM person; SELECT ('1')

This code will run without error and it'll delete everything in the
person table, obviously not what the application intended.  This
is the sort of bug that Bad Guys try to exploit.  When you hear
about "SQL injection" this is what they're talking about.

Data, especially if it comes from an untrusted source, should be
properly quoted.  How to do this depends on the language and API:
some APIs support placeholders, while others provide a quoting
function.  PHP, for example, has functions like pg_escape_string()
and addslashes().  In PHP's case, see chapters like "Database
Security," "User Submitted Data," and "Magic Quotes" in the Security
part of the PHP documentation, as well as the documentation for the
database API you're using (native PostgreSQL functions or otherwise).

> another issue i'm debating right now is how to handle
> the database connection parameters (dbname, host,
> username, pw).  should that be included in a function?
>  and include?  both?

Connection parameters in web-based scripts should be imported from
outside the document tree so they can't end up on a web page.  For
example, a misconfiguration in the web server might disable PHP,
so instead of your code running the user will see your PHP source
code.  If the code contains passwords then they'll show up too.

On shared web servers it's common that everybody's code runs with
the privileges of the web server user, so anything that you can
read or write can also be read or written by other users.  This has
security implications if you need to read sensitive data like
passwords from a file.  How to mitigate that problem depends on the
web server and is getting off-topic for PostgreSQL.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/