Querying with user input

[webenveloper]

Hello all,

How many ways are there to put variables into a query?
The reason I ask is that I cannot get

"SELECT * FROM locations WHERE title LIKE '%".$input."%'";

The "SELECT * FROM locations" query only does give me the full table but using that input variable substitution (from an input field) kills my query and returns nothing. (yes the tables are full and contain matches haha)

How can I get this to work with POST data, and what other methods can us noobs use to add variables to our queries?

I've seen:

:variable , {'$variable'} , "%'.$input.'%" , ('$searchquery') etc

I'm sure there is more but I'm sure this will be educational to see WHY we would use the differences. Thanks!

[niranga]

"SELECT * FROM locations WHERE title LIKE '%".$input."%'";

The "SELECT * FROM locations" query only does give me the full table but using that input variable substitution (from an input field) kills my query and returns nothing. (yes the tables are full and contain matches haha)

Just check whether $input is set or not. If you are trying to get values from POST data, try using $_POST['field_name'] directly just to make sure you are getting POST values and check whether you are assiging $_POST['field_name'] to $input

[Marcus mouse]

Hi. Assuming that you have $_POST variable called "what" for example that you want to use in your query,
it's a good idea to check if it is set first and the assign it to another variable e.g. $var_what. so...

if isset($_POST['what']){
    $var_what = $POST_['what'];
}

and then you can include that variable in the mySQL query.

   SELECT * FROM locations WHERE title LIKE '%{$var_what}%'";

[pritaeas]

:variable

Definitely preferred (or ?), because it indicates that you are using bound parameters. That drastically decreases the change of an SQL injection. The other three rely on variable insertion, which need decent validation before using them.

[webenveloper]

Thanks everyone. I did test to see if the POST was working BTW. You know what my problem was....? Charset... It wasn't UTF8. I learned a valuable lesson on this one.

Does anyone have any resources to understand more about bound parameters? I still don't understand much about these insertions.

Thanks!

[webenveloper]

Have a question on the same subject. Inserting POST data into a PDO prepared statement obviously doesn't work the same way. Any advice on how to make input data like I mentioned above pass to PDO SQL?

$sql = "SELECT * 
FROM beaches 
WHERE 
(title LIKE '%".$input."%') 
OR 
(address LIKE '%".$input."%')";

$stmt = $pdo->prepare($sql);

$stmt->execute( array() );

$results =  $stmt->fetchAll( PDO::FETCH_ASSOC );

Is this an instance where you must use :$input ? And why?