Here is a snippet from an SQL statement in my PHP…
WHERE ap.sd_section_slug = 'finance'
AND ap.ds_dimension_slug = 'featured_finance'
Is there some way that I can make the 2nd value in my WHERE clause “partially-dynamic” like this…
WHERE ap.sd_section_slug = $section
AND ap.ds_dimension_slug = 'featured_[COLOR="#FF0000"][B]<Insert value from $section variable here>[/B][/COLOR]'
The goal is that if a user is clicking on different “Section” tabs, I want my query to change like this…
WHERE ap.sd_section_slug = 'finance'
AND ap.ds_dimension_slug = 'featured_finance'
WHERE ap.sd_section_slug = 'legal'
AND ap.ds_dimension_slug = 'featured_legal'
Once user input is accepted in queries you should be using variable binding. Especially, since you’re using PDO. I know your next question is going to be how that is done.
You could also use escaping as one might with the traditionally php database layer mysql_* functions but that kind of defeats the purpose of using the PDO adpater in the first place.
Here is what I came up with to preserve how my Prepared Statements are supposed to work…
// Set Variables.
$sectionSlug = $_GET['section'];
$dimensionSlug = 'featured-' . $sectionSlug; //APPEND THINGS BEFOREHAND
// Build query.
$q1 = "SELECT ss.name
FROM subsection AS ss
INNER JOIN dimension_subsection AS ds
ON ss.slug = ds.subsection_slug
INNER JOIN section_dimension AS sd
ON ds.dimension_slug = sd.dimension_slug
WHERE sd.section_slug = ?
AND sd.dimension_slug = ?
ORDER BY sd.section_slug, ss.sort"; //USE QUESTION MARK IN BOTH PLACES VARIABLES ARE NEEDED
// Prepare statement.
$stmt1 = mysqli_prepare($dbc, $q1);
// Bind variable to query.
mysqli_stmt_bind_param($stmt1, 'ss', $sectionSlug, $dimensionSlug); //BINDING AS NORMAL
This seems to be working, and should be much more secure than the original solution.
You don’t really need them in this case but I like them as they highlight the variables inside the string.
Variable parsing
When a string is specified in double quotes or with heredoc, variables are parsed within it.
There are two types of syntax: a simple one and a complex one. The simple syntax is the most common and convenient. It provides a way to embed a variable, an array value, or an object property in a string with a minimum of effort.
The complex syntax can be recognised by the curly braces surrounding the expression.
Complex (curly) syntax
This isn’t called complex because the syntax is complex, but because it allows for the use of complex expressions.
Any scalar variable, array element or object property with a string representation can be included via this syntax. Simply write the expression the same way as it would appear outside the string, and then wrap it in { and }. Since { can not be escaped, this syntax will only be recognised when the $ immediately follows the {. Use {\$ to get a literal {$. Some examples to make it clear:
