0

I couldn't find a definite answer to this specific issue, basically what I have created is a HTML table which displays the contents of a database into columns and rows. Currently it is very simple as I am understanding how it works for now.

ID | First_Name | Last_Name | Bio | Created

ID = Primary Key for each row of the database.

I wish to be able to update an entire row of the database with one update command, if this is possible. Currently I am using:

$sql = "UPDATE databasetest.people SET First_Name = '".htmlspecialchars($_POST['FirstName'])."' WHERE people.ID = ".$_POST['edit'];

...where $_POST['FirstName'] is the first name, as defined by a form and $_POST['edit'] is the ID as defined by an initial form where the user selects "Edit User".

Is it possible to update multiple columns (first name, last name, bio and created at once) in this way?

EDIT: Very quick answers, thank you very much! I am not sure why I was downvoted, possibly because it was dumb? Sorry, still learning :)

lukesrw
  • 1,692
  • 1
  • 14
  • 20
  • Yes, use a `,` after each value `UPDATE databasetest.people SET First_Name = '".htmlspecialchars($_POST['FirstName'])."', lastname = 'whatever' WHERE people.ID = ".$_POST['edit'];` https://dev.mysql.com/doc/refman/5.0/en/update.html – user3783243 May 12 '15 at 17:26
  • 1
    @user3783243 it's best to answer the question in an answer rather than a comment. It's an SEO thing. – O. Jones May 12 '15 at 17:27
  • @OllieJones wasn't certain this was the issue. Isrwluke you should separate out user input from your queries. This is how SQL injections occur. You were probably downvoted because this is one of the first things on the manuals page. – user3783243 May 12 '15 at 17:32
  • 1
    Incorporating potentially "unsafe" values in SQL statements creates **SQL Injection** vulnerability. Consider what happens in your code (what SQL statement your code issues to the database) when a nefariously clever user sends a request with the value of `edit` set to a string like "**`1 OR 1=1`**". – spencer7593 May 12 '15 at 17:44
  • Couldn't htmlspecialchars get rid of the injections for the most part? Anyway... this will only be a select few people using this in the end. – lukesrw May 12 '15 at 17:45
  • 2
    Unless the page is secure anyone can execute it that finds it. htmlspecialchars only encodes special HTML characters `<, >, &`. http://php.net/htmlspecialchars http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – user3783243 May 12 '15 at 17:47
  • No, using `htmlspecialchars` does not eliminate SQL injection vulnerabilities. It might close a couple of holes, but potentially unsafe values incorporated into SQL text *must* be properly escaped. Consider the SQL statement generated when `FirstName` has a non-malicious value of "**`O'Reilly`**". (HINT: That single quote character is going to end the string.) `htmlspecialchars` is *not* suited to the task of preventing SQL Injection. – spencer7593 May 12 '15 at 17:52

1 Answers1

6

You want

  UPDATE table
     SET col='val', col2='val2', col3=123
   WHERE ID = 345

See how you can use commas to separate multiple columns and the values they should get?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thank you very much! I wasn't sure how it would work in terms of formatting to simply add a comma... that clears that up. – lukesrw May 12 '15 at 17:30