Topic: Safe inserts?

[clay1]

This seems like something that should be easy to find, but I've been searching for a week for an answer.

I've got a site that has a public form that is filled out by unknown people.

I want to make sure my database and information is as safe as possible from attacks.

So I've got my form data in the post array. I need to check it to make it sure it is valid.

I've got most of that down.

But then what?

How do I get the information into my database in an efficient but secure way?

[btherl]

You can use pg_escape_string on the date, then enclose it with single quotes.  Something like this:

[php]$sql = "INSERT INTO tab (col1) VALUES (E'" . pg_escape_string($data) . "')";

The "E" says that there's an escaped string following.  You can leave it out and it'll generally work, but newer versions of postgres will generate a warning.

For data that should be a specific data type (such as an integer), you can filter the string so it only contains digits, for example.

[clay1]

Code: [Select]

[php]$sql = "INSERT INTO tab (col1) VALUES (E'" . pg_escape_string($data) . "')";

Thanks.

So would I repeat this line for each column? I've got about 30 columns.

Or could I do a loop on something like:

Code: [Select]

[php]$sql = "INSERT INTO tab ($key) VALUES (E'" . pg_escape_string($data) . "')";

[clay1]

Still struggling with this problem

[btherl]

Columns get added all with one statement.  For example:

$sql = "INSERT INTO tab (col1, col2, col3) VALUES (E'" . pg_escape_string($col1_data) . "', E'" . pg_escape_string($col2_data) . "', E'" . pg_escape_string($col3_data) . "')";

If you need to add multiple rows, then you should use a loop for that.  Just not for the columns (at least not normally)

[clay1]

I ended up using pg_insert

Which word has it is injection safe. Guess time will tell

[btherl]

pg_insert()?  I didn't know that existed

Judging by the comment in the example in the php docs, it's injection safe.  But the function is also labelled as experimental

Anyway if it IS telling the truth and it is safe, then you definitely must NOT call pg_escape_string() yourself, as otherwise you'll get your strings escaped twice.  That's a real hassle when that happens.

[clay1]

Yeah I removed the pg_escape_string after I checked my data

I've been testing pg_insert and pg_update and both have been working so far.

Still need to build in my verification and some other security stuff but basically taking running the two on $_POST works(key names need to match column names)