MySQL inputting TINYINT syntax

Question

I created TINYINT columns in my MySQL database to interpret boolean variables, which based on whether checkboxes are checked or not in the html store the values (0 for false, everything else for true) in the DB. But it won't update the values when the php file is called. Is there something wrong with my SQL? Are TINYINTs inputted as below? Simply with a 0 and a 1?

<?php
include_once("createConnection.php");
session_start();
$checkbox = $_POST['name'];
$checked = $_POST['checked'];
$currentUser = $_SESSION['validUser'];

if($checked=='yes'){
    $request='UPDATE projectDB.Members 
        SET :name=1 WHERE username=:currentUser';
    $preparedStatement = $bdd->prepare($request); 
    $preparedStatement->bindParam(':name', $checkbox, PDO::PARAM_STR);
    $preparedStatement->bindParam(':currentUser', $currentUser, PDO::PARAM_STR);
    $preparedStatement->execute();
}
else{
    $request='UPDATE projectDB.Members 
        SET :name=0 WHERE username=:currentUser';
    $preparedStatement = $bdd->prepare($request); 
    $preparedStatement->bindParam(':name', $checkbox, PDO::PARAM_STR);
    $preparedStatement->bindParam(':currentUser', $currentUser,     PDO::PARAM_STR);
    $preparedStatement->execute();
}
?>

what is the name of your tinyint column ? `:name` (syntax wise) should be a column name (as defined in your table schema, not a value. — YvesLeBorg, Mar 12 '17 at 02:40

Dan Field · Accepted Answer · 2017-03-12T02:43:40.497

Normally you want to parameterize your queries like this when you're taking user input. Your problem is that the user input (or at least potential user input) you're taking in is a column name rather than a value. PHP is converting your query to something like this:

UPDATE projectDB.Members 
    SET 'name'=1 WHERE username='currentUser'

Which doesn't do what you want it to (it's telling SQL to update a string called 'name' instead of update a column called name).

You still run a risk here if you don't sanitize your data - you basically have two options:

Have a whitelist of acceptable column names in your code; verify that the incoming string matches an entry in that whitelist exactly, if so use it as a column name. The disadvantage here is that you have column names in your data model strewn about your code and HTML. e.g.:

$chkcols['name1'] = true;
$chkcols['name2'] = true;
$chkcols['name3'] = true;

...
if ($chkcols[name] == true) ...;

or

Come up with a different data model, maybe something like EAV, where you don't have to deal with dynamic column names. Disadvantage here is that EAV can be a bit of an antipattern in SQL. This might use a query something like: UPDATE projectDB.Members SET Enabled = 1 WHERE name=:name AND username =:currentUser; you could, alternatively to an EAV model, have a column that has preferences or a bitmask/list of some sort (but again, this is a SQL antipattern in that you're trying to pack in multiple pieces of information in a single column.

There are only a limited number of possible checkboxes so hard coding it with a switch statement is the best option in this case. I did not realise column names could not be passed as variables. Thanks! — AndrewY, Mar 12 '17 at 11:23

MySQL inputting TINYINT syntax

1 Answers1