PHP MySQL Adjusting Query with QueryString

Question

Apologies for the newbie question.

My website has a form.

<form action='' method='get'>
<select id="cSelector" name="cSelector">
    <option value=""></option>
    <option value="">Show All Items</option>
    <option value="Compensation">Compensation</option>
</select>
  <input type="submit" value="Submit">  

</form>

My querystring, created on form submission, looks like this:

http://website.com/table_example.php?cSelector=Compensation

My query looks like this:

$stmt = $conn->prepare("
SELECT t1.CategoryID,t1.SubCategoryName, t1.CategoryName, t1.SubCategoryID, t2.ItemText from
    (SELECT Category.CategoryID,CategoryName, SubCategoryName, SubCategoryID
    FROM Category
    JOIN SubCategory
    ON Category.CategoryID = SubCategory.CategoryID) t1
RIGHT JOIN  
    (SELECT SubCategoryID, ItemText FROM Item) t2
ON (t1.SubCategoryID = t2.SubCategoryID)
WHERE 1 ".$searchQuery." AND CategoryName = ".$search2." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

The intended result produces a table queried by CategoryName.

My question. Why does this properly execute?

$search2='Compensation';

And this does not?

$search2 = "'".$_GET['cSelector']."'";

Any help would be very much appreciated. And thank you!

you're submitting this form via `GET` but in `$search2 = "'".$_POST['cSelector']."'";` you're using `$_POST`. Try `$_GET` instead. — dazed-and-confused, Dec 22 '20 at 20:54
Also, not related to your question, but you should use query parameters. It's way easier and safer than all that quote-unquote-dot-variable-dot-quote-unquote mess. Do yourself a favor and reduce errors and eyestrain! [Use query parameters!](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — Bill Karwin, Dec 22 '20 at 20:57
@ dazed-and-confused and @ Bill-Karwin am also learning prepared queries. But, one thing at a time. Much appreciated advice. — David Weisser, Dec 22 '20 at 20:59

dazed-and-confused · Accepted Answer · 2020-12-22T21:02:02.043

0

You're submitting this form via GET

<form action='' method='get'>

Your line though $search2 = "'".$_POST['cSelector']."'"; is using $_POST

It should be $_GET instead:

$search2 = "'" . $_GET['cSelector'] . "'";`

AFTER OP's CHANGES

This

$search2='Compensation';

and

$search2 = "'".$_GET['cSelector']."'";

are not the same. The top is just a string value. The bottom is a string value wrapped in quotes, so it isn't Compensation it is 'Compensation'.

edited Dec 22 '20 at 21:02

answered Dec 22 '20 at 20:55

dazed-and-confused

1,293
2
11
19

Edited Question. Caught me in an iteration. Trying every variation I can. Both the Form and the PHP are GET and the query doesn't return results. Empty Table. – David Weisser Dec 22 '20 at 20:59
1

I am using a piece of code named DataTables, which is a JS script I downloaded. I think by editing the original query, I am causing problems in the script. You're answer is correct. I wanted to avoid having to learn to program the table with search from scratch....but I think I may have to. Thank you all. – David Weisser Dec 22 '20 at 21:06

score 0 · Answer 2 · answered Dec 23 '20 at 00:49

The core of the issue is actually that you're not exactly sure what the query is. If the two strings sent were identical, they would both run, but they're not. Somehow.

The real need is visibility into your query. So something like

$strQuery = "SELECT t1.CategoryID,t1......";
echo "<pre>$strQuery</pre>";
$stmt = $conn->prepare($strQuery)

Now you can see what it's doing. You're operating blind as it is.

Two additional notes:

You'll hear from everyone that it's a bad idea to put paramaters you're getting from a get or post straight into a SQL query. They're not wrong.
String building for these things is always easier if you're a little more verbose about it. Grab the variable first, as you're going to want to do some processing on it anyway, trimming whitespace, protecting against quotes, etc. Then put it in your query string

PHP MySQL Adjusting Query with QueryString

2 Answers2