Copying data from one column to another but changing datatype using SQL in PHP

Question

I am trying to copy a column of data (which is numeric) to a different column (VARCHAR) in the same table. Seems straight forward to just run an update query and set the varchar column to whatever the $_POST column will be. The problem is numeric columns will contain 5 trailing zeros. I need to reformat it to be an INT type in the copy as well. Here is a copy of the original query in PHP:

try{
    $q = "UPDATE table1 SET text1 = " . $_POST["htmlinputfield"] . 
        " WHERE account_id = :aid AND fieldapplication_id = :fid";
    $stmt = $this->app->db->prepare($q);
    $stmt->execute(array(":fid"=>$this->app->fid,":aid"=>$this->app->aid));
} catch(exception $e){
    $this->app->logErroredQuery($e,$q);
    return $e->getMessage();
}

I tried changing the datatype using php in an if statement then sticking into the query. Couldn't get that to work either. Here is what that looked like:

$htmlfield = $_POST["htmlinputfield"];
    
if(is_numeric($htmlfield)){
    (int)$htmlfield;
}else{
    (string)$htmlfield;
}
    
try{
    $q = "UPDATE table1 SET text1 = :htmlfield 
          WHERE account_id = :aid AND fieldapplication_id = :fid";
    $stmt = $this->app->db->prepare($q);
    $stmt->execute(array(":fid"=>$this->app->fid,
                        ":aid"=>$this->app->aid,
                        ":htmlfield"=>$htmlfield));
} catch(exception $e){
    $this->app->logErroredQuery($e,$q);
    return $e->getMessage();
}

This resulted in changing all of the results to be the name of the column instead of a copy of the data. The original query works fir everything but numeric situations.

I imagine that this might be a CAST situation but my SQL abilities are not very good. The end result would be that whatever the database column that is being selected in the $_POST will be formatted to an int or a varchar. Any help would be much appreciated.

user_2734189 · Answer

The first query with $q = "UPDATE table1 SET text1 = " . $_POST["htmlinputfield"] is the general way this needs to be done. You will need to insert the column name directly into the SQL string, but it's not safe to blindly use a value from $_POST like that. You should verify that $_POST["htmlinputfield"] is a valid column name before you use it in the query. You can use a whitelist method and check it against an array of acceptable column names in your PHP code.

The second way with $q = "UPDATE table1 SET text1 = :htmlfield can't work, because the column name is an identifier, and you can't bind identifiers, only values.

This part:

$htmlfield = $_POST["htmlinputfield"];
    
if(is_numeric($htmlfield)){
    (int)$htmlfield;
}else{
    (string)$htmlfield;
}

does not really make sense, because $_POST["htmlinputfield"] is the column name if I understood you correctly, and using is_numeric() in PHP isn't going to tell you what the data type of that column is, it's just going to check if the column name is numeric.

I think one way to solve this is to check if the value is numeric in your update query and remove the decimal places if so, otherwise take it as is. So first validate the posted column name as I mentioned above. After you've done that, you can safely use it in the query like this:

"UPDATE table1 SET text1 = CASE
   WHEN `$column_name` REGEXP '^[0-9]+(\.[0-9]+)?$'
   THEN FLOOR(`$column_name`)
   ELSE `$column_name`
 END
 WHERE account_id = :aid AND fieldapplication_id = :fid"

There are different ways to check if a value is numeric, and different ways to convert the value to int, but I think the general idea will still be something like this.

user_9072332 · Answer

Try the following query in your last example:

UPDATE table1 SET text1 = CAST(:htmlfield AS UNSIGNED) WHERE account_id = :aid AND fieldapplication_id = :fid

Copying data from one column to another but changing datatype using SQL in PHP

2 Answers