How do you avoid hard coding SQL statements in PHP?

March 30, 2009 11:00 AM

Is there a good way to prevent hard coding of insert/update sql statements in PHP?

I'm writing a data-driven PHP application. In several cases, there is a large SQL table, and different users are responsible for submitting different fractions of the data (i.e. Alice submits 3 of a row's fields, Bob submits 4 other ones). There are also cases where the web application modifies all the fields in a row. I've been hard coding the INSERT and UPDATE statements for each form handler which feels like bad design (especially if new database fields are added in the future). Does anyone have experience with this type of problem? Any good solutions?

posted by secret.osha to Computers & Internet (5 answers total)

Depending on the SQL server you are using, Stored Procedures is the way to go for that problem. Here's a quick link i googled that gives the overview for PHP and MS SQL : SitePoint Article.

Basically, you write the inserts / updates in the SQL engine, and then call them almost like a function from PHP. If you need advice on the PHP - MySQL link, mefimail me, but it's very similar to the MSSQL.
posted by frwagon at 11:04 AM on March 30, 2009

Another way is to use an Object Relational Model like Doctrine, or a framework that includes a database abstraction layer like the aforementioned Symfony or Zend Framework. I think PHPIgniter and CakePHP also include these features, but my experience is mostly with Symfony and Zend Framework because they have more advanced features that only come with PHP5.
posted by SpecialK at 11:35 AM on March 30, 2009

Best answer: If you have an array of fields and an array of values, you can use sprintf to dynamically insert them into the SQL without too much trouble. It's less work than learning and using a whole framework, but you do have to be a little careful about pre-escaping your data. As you're walking along your conditions, just append stuff to the $fields and $values arrays until you have everything you're ready to stick everything into the database. Making $values another array deep and doing a little more string massaging can you get into multi-row insert land, too.

$fields = array("name","dob","email");
$values = array("'soma lkzx'", "12-12-1900","'[e-mail]'");

if($is_bob) {
    array_push($fields, "height");
    array_push($values, 145);
}

$sql = sprintf("INSERT INTO tablename (%s) VALUES (%s)", implode($fields, ","), implode($values,","))

I am not a PHP coder by any means and this is just back-of-the-napkin code. You could also take advantage of key-value pairs in associative arrays instead of having separate $fields and $values.
posted by soma lkzx at 11:46 AM on March 30, 2009

PDO ships with PHP 5.1 (and higher) so you might already have it. It parameterizes and prepares plain old fashioned queries for execution. It's fairly easy (as in not much work) to refactor an existing codebase to use, and it handles things like properly escaping strings to prevent sql injection.

A simplified but not atypical usage would be something to the effect of:

$statement = $db->prepare( "select * from foo where bar = :baz" );
$statement->bindParam( ':baz', $baz, PDO::PARAM_STR );
$statement->execute();
while( $row = $statement->fetch( PDO::FETCH_ASSOC ) ) {
echo $row['columnName'];
}

posted by cCranium at 4:25 PM on March 30, 2009

Yeah, prepared statements or stored procs. Call either like a function; in fact you'll wat to make them only accessible through a function that validates/sanity checks the data (or at least the data's type, given that PHP is weakly typed), so they can only be called with valid data.

Note that for selects with optional predicates, you can still have one SP, by using the null parameter trick. Consider the following sql statements:

select * from person where lastname = ?;
select * from person where lastname = ? and firstname= ?;
select * from person where firstname= ?;

Can you write this as one SP, rather than as three? Yes.
Can you do that without putting all three statements and three "if" statements in the SP? Also yes.

create proc find_person( @ln varchar, @fn varchar) as
begin
select * from person where ( lastname = @ln or @ln is null) and ( firstname = @fn or @fn is null);
end;

Then pass null for the parameters you don't care about, the values that should be "unbound".

While this'll work on any RDBMS, some optimize the null out better than others (Sybase optimizes it well, MSSQL Server not so well), so if you do this, run a showplan.

For prepared statements, this is less necessary, as you can create the prepared statement dynamically, while dynamically creating an SP shouldn't be allowed.
posted by orthogonality at 7:40 PM on March 30, 2009