PHP MySQL Update Statement Won't Accept Anything But Numeric Values

Question

I have the following sql update statement and the variables are being sanitized prior. The information is coming from a form and the database columns are TEXT. INSERTS work flawlessly.

DB Structure:

file_title=text file_description=text id=INT

mysqli_query($con,"UPDATE images
        SET file_title=$FileTitle, file_description=$FileDescription
        WHERE id=$image_id");

For some unknown reason I can only update using numeric values as it just won't update using any text which is frustrating.

I must just be missing something really basic. Any advice would be appreciated.

Answer 1

You need to quote text and string values:

mysqli_query($con,"UPDATE images
        SET file_title='$FileTitle', file_description='$FileDescription'
        WHERE id=$image_id");

Numeric values could be passed without quoting.

Answer 2

because string needs to be wrapped with single quotes. Single quotes are for string literals while backtick are for identifiers,

UPDATE images
SET    file_title='$FileTitle', file_description='$FileDescription'
WHERE id=$image_id

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

• How to prevent SQL injection in PHP?

Answer 3

Use single quote for text values.

mysqli_query($con,"UPDATE images
        SET file_title='$FileTitle', file_description='$FileDescription'
        WHERE id=$image_id");

Answer 4

CHAR values must be quoted, but more importantly you should be using prepared statements:

$stmt = mysqli_prepare($con,"UPDATE images
    SET file_title=?, file_description=?
    WHERE id=?");
mysqli_stmt_bind_param($stmt, 'ssd', $FileTitle, $FileDescription, $image_id);
mysqli_stmt_execute($stmt);