Web Developers → Dynamic Sql and PDO's bindParam. How to get this to work?

In my script, I generate sql based on what comes in from the url and thru htaccess

So:

Http://www.domain.com/us/2013
 

If (isset($_GET['country']) && isset($_GET['year'])) {
  $tab = 'country = $_GET['country'] and year = $_GET['year']';    
} elseif (isset($_GET['country'])) {
  $tab = 'country = $_GET['country']';    
} else {
  echo 'Error';
}
 

Then I do:

$sql = 'select * from table where :sub;
 
$fetchData = $conn->prepare($sql);
$fetchData->bindParam(':sub',$tab);
 
//I was thinking of using the below, but it wont work.
$fetchData->execute(array(
 
':country' => $location,
 
));
 

Somehow, this will not work, even though the entire sql statement gets constructed. Is there anyway I can do something like this using PDO? This is the only thing that's standing in the way right now.

I am not familiar with $_GET() but that looks to be an SQL injection attack waiting to be discovered, that is the point of bindParam, to separate the sql statement structure from the parameters that are not necessarily clean.

So how could I do something like this? Or should I stick to the old non PDO?

Two different sql statements, one doing country and year, the other doing country. Or one sql statement something like this

select _ from _ where (A=? and B=?) or (?="" and A=?)

I see that I'm gonna have to redo this entirely.

NEVER, EVER PASS FORM PARAMETERS TO SQL STATEMENTS.
That operation should be done via a stored proc, there is absolutely no excuse not to do it.

This part is true; bound parameters are necessary for security. Stored procedures don't add anything to security beyond what bound parameters provide.

Those values come from a htaccess file.

While it's better than nothing, it's still not a good practice. While YOU know how things are working and what protections are in place, the guy who comes along behind you to maintain your code might not. Or the code gets moved to a different environment where the htaccess isn't setup correctly.

Not only security, code maintainability too... One day someone may decide to upgrade to MSSQL, if done properly the code change would be just few lines in the db interface layer.
It is always a good idea to separate the database/business/presentation layers, makes everything easier, from programming to q/a and deployment.

In my experience, utilizing stored procedures causes the exact opposite to happen if someone does a database switch between providers, ie MySQL -> MSSQL. Where using a persistence layer or something similar can allow for a more seamless transition. You can still separate out the database/business/presentation layers without making use of Stored Procs.

Not if you're including server-specific statements in the code - ex an IFNULL vs ISNULL. You can even change the entire data structure with absolutely no code change by using stored procedures and views.
When you are completely separating code each piece can be tested and modified independently.

Everything I've read hates stored procedures, but maybe I only read the writing from certain kinds of developers.

They are useful for one thing though: Percona uses stored procedures to allow an ALTER table on a huge table to run over many days while the production database keeps chugging: the script uses the stored procedure to capture updates and so on continuously, and does the alter table in chunks.

Then the stored procedure is deleted at the end, I like that part the best.

This is my feeling too. Especially those who don't develop in a large environment...

intuitively to me it seems that putting logic into the database indicates a certain failing of the development language: mixing oil and water. From a performance perspective I can see the argument, but that's it, and performance is cheap nowadays.

I use stored procedures some some kinds of applications, mainly to encode business logic that has to be called from more than one place. This is exactly the right reason, but I'd never wrap every possible query in a stored procedure.

that's why there should be a layer, so N big apps call through the layer that does the abstraction. It seems to me a stored procedure is a convenient shortcut but the price is paid. especially in large developments.
db is for persistence, source is for logic. I know that's idealistic but architecture is idealistic. A big project has the resources to enforce these divisions.

This is what I've seen in my experience. Though I've been fortunate enough to be working with applications that did not require a large amount of this, and when we did the databases were more than enough to handle the load.

Abstraction layers are great and all that, but what if you need to routinely interact with the DB at the command line? This really does happen in real apps, and being able to interact with the DB and have key biz rules enforced is a huge win; I'd have to have to write an app every time I wanted to do something ad-hoc.

It's not performance and it's not failing of the development logic.
It's just to segregate the database and the app. You can independently do unit testing of the stored procedure and of the app logic. You can migrate the data to a different structure and do it completely transparent to 100 applications that may be using that data.
Think of a table where you store customer attributes in a column-based format. You can move to a row-based format without having to change and re-test all 100 apps. All it takes is writing a new SP that takes the same params and returns data in the same format. You can move the customer data to a whole different database or even to a different server without impacting a single app.
So you don't call "select status from customers where customer='abc'" from your code, you call "proc_GetCustomerStatus 'abc'" - same result but when you want to change your data structure you can simply see the dependencies and pinpoint the 37 procedures using the table, vs. guessing what apps may be using the table...

You're writing code in a particular environment, the programmers are most comfortable and trained with that environment, the unit tests use that environment, the database stores the data, that is its job, the SQL admin makes sure the database is fast, the indexes are right, and does the migrations to better layouts.

But now enter stored procedures. Suddenly the code you look at is not the whole picture, the stored procedures are in a different (and clunky) language, the SQL administrators are now programmers, or is it that the programmers are now DB Admins? the source control system has to integrate perfectly.

I'm not saying that stored procedures are not useful, I'm saying that I don't see they are a necessary tool, I think they may be a hangover from a different era and were at least partly created to solve issues that don't exist now, and I'm sure a lot of very large projects can avoid them and end up ahead.

In other words if there is a choice I think it is ideal if they are used sparingly, to not at all. It might be idealistic but it seems reasonable to me if there is an alternative, at any point, to pick the choice that doesn't add another stored procedure.

btw this has wandered way off topic, I think the upvoted posts on this page
»programmers.stackexchang ··· nsulting
say better than I am able to here what is "wrong" with embracing stored procedures.

IMO it all comes down to development style. There is an inherent disconnect between the relational model and OOP and there is no one right way to address that.

There is a legitimate use for stored procs though. The one relationship stored procedures were meant to address is the mandatory 11 relationship, which can only be done by a transaction. Anally retentive DBA's (that's me) would make sure this happened by denying access to the base tables involved and only allow inserts/updates/deletes through stored procs.

Not that I don't trust programmers, most are good to excellent. Sadly it only takes one to issue some bad DML...

What planet are you from?

LMAO. Our office has a no-weapons policy, and now I'm sure that it's for a reason...

Ok, so most of the programmers I work with...