Code for Updating MySQLI PHP

Question

A few keep telling me that my code for updating data in my mysqli query is extremely insecure. Actually, several people on this site. So I would like to know what they say would secure my code below so it is secure when updating my database. I would like to know how the would secure my mysqli query.

Okay, in my code for my database entries, this is what I do. Let me start by saying that I always send via POST method to avoid browser url complications.

When I get the POST data, this is my code.

$ID = 1;
$DATA = htmlentities(addslashes($_POST['data']));
$FIELD = "lifename";
$DBQUERY = "UPDATE `lifetable` SET `$FIELD` = '$DATA' WHERE `id` = $ID";
$DBRESULT = $MYSQLI->query($DBQUERY);

I am currently using this on my local site.

How is this unsafe if I have escaped all quotes, all slashes, all ampersands (from javascript through ajax) and all semi colons? How is this vunerable?

So can you tell me what I should change when adding information to my database.

Thanks

PS ... I am using mysqli and will continue to use it. Thanks

Try to get use of PDO Statements for DB queries. Then you are on the save side. http://php.net/manual/en/intro.pdo.php — Jurik, Dec 09 '13 at 10:39
http://forums.mysql.com/read.php?52,227585,227585 please read this article you may get good idea after that use pdo since its good abstracting db queries instead of vendor specific function mysql or mysqli — gvgvgvijayan, Dec 09 '13 at 10:41
There is a way to secure mysqli, so you never even came close to answering my questions, my tage specifically says mysqli. I was asking how to make my mysqli statement more secure. — Denver William, Dec 09 '13 at 10:56
Duplicate for the most upvoted question under the tag, http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Your Common Sense, Dec 09 '13 at 10:58

score 1 · Accepted Answer · answered Dec 09 '13 at 11:47

A few suggested that I change from mysqli to pdo, but I am not willing to completely 100% change how I access data from my databases. Someone posted another link before about prepare and bind_param and this is what I am going to use. So thank you.

This is now my code, and binding params is supposed to make it so that each param is only for the one part of my query and can not be for anything else, nothing else at all.

    $DBQUERY = "UPDATE `lifetable` SET `lifename` = ? WHERE `id` = ?"; // EACH ? IS A PART OF bind_param BELOW IN ORDER AFTER TYPE.

    $STMT = $MYSQLI->prepare($DBQUERY);
    $STMT->bind_param('si', $DATA, $ID); // THIS MAKES SURE THAT THE VARIABLES ARE ONLY USED FOR THERE PLACE HERE AND NOTHING ELSE. ? in order.

    $DATA = htmlentities($_POST['data']); // I STILL USE MY CODE HERE TO REMOVED ANY OTEHR CHARACTERS, JUST INCASE. AND BEFORE IT GETS HERE, IT USES encodeURIComponent TO OUTPUT FROM AJAX.
    $ID = $COLUMN[1];

    $STMT->execute();
    $STMT->close();

My code worked before and it works now, just more secure, or so I am told.

score 0 · Answer 2 · edited Dec 09 '13 at 11:04

0

i think your security lies in the SQL injection, and the best way i know to make the query secure is using mysql_real_escape_string on the var. Here an example taken from php.net

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");
$city = $mysqli->real_escape_string($city);
$mysqli->query("INSERT into myCity (Name) VALUES ('$city')")

you can apply the same procedure to your query

$ID = 1;
$DATA = $MYSQLI->real_escape_string($_POST['data']));
$FIELD = "lifename";
$DBQUERY = "UPDATE `lifetable` SET `$FIELD` = '$DATA' WHERE `id` = $ID";
$DBRESULT = $MYSQLI->query($DBQUERY);

I edited the above because I forgot the quotes for lifename in my question. They should be there as they are in my original code.

now tour query should be secure :D here the reference to php.net documentation : http://cn2.php.net/manual/en/mysqli.real-escape-string.php

edited Dec 09 '13 at 11:04

Denver William

464
2
11

answered Dec 09 '13 at 10:48

Aliceiw

420
5
19

As long as `lifename` is not a constant, this code will throw an error. And best way to avoid SQL injection is to use the PDO Class. Because it prevents all the problems you can have. – Jurik Dec 09 '13 at 10:52
1

true i just copied the code above without checking the input assuming it was already working XD – Aliceiw Dec 09 '13 at 10:53
and why is not? $ID and $FIELD are hardcoded so i suppose is property of a class or something the only sensible data is $_POST['data'] and mysql_real_escape_string provide security against sql injection... – Aliceiw Dec 09 '13 at 10:56
I do not have an error with my code, my code works fine, I did not say I had an error in my code, I said I wanted to make it more secure, and I do not wish to use PDO. @Jurik – Denver William Dec 09 '13 at 11:04
@YourCommonSense incase you never noticed, that was what my question was about !! And if you want to critique, then please provide an answer of your own. Thanks – Denver William Dec 09 '13 at 11:05
How is this unsafe if I have escaped all quotes, all slashes, all ampersands (from javascript through ajax) and all semi colons? How is this vunerable? – Denver William Dec 09 '13 at 11:09
just found another answer on stackOverflow that explain in detail sql injection http://stackoverflow.com/questions/134099/are-pdo-prepared-statements-sufficient-to-prevent-sql-injection/134138#134138 and assuming that $ID and $FIELD are hardcoded or managed internally it should be secure. – Aliceiw Dec 09 '13 at 11:11
@Sakuya84 My $ID does not come from a user input, that is why I am not concerned about that. My data is the only thing that comes from the user. – Denver William Dec 09 '13 at 11:11
then you should just have a fast read about the other answer i just posted in the comment and verify that one of this condition are true : Use Modern Versions of MySQL (late 5.1, all 5.5, 5.6, etc) OR Use mysql_set_charset() / $mysqli->set_charset() OR Use the DSN charset parameter to PDO OR Don't use GBK or BIG-5 (you only use UTF-8 / UCS-2 / Latin-1 / ASCII) – Aliceiw Dec 09 '13 at 11:14

score 0 · Answer 3 · edited May 23 '17 at 12:13

0

Use PDO Class like:

$db = new PDO('mysql:host=localhost;dbname=<SOMEDB>', '<USERNAME>', 'PASSWORD');
$query = $db->prepare('UPDATE `lifetable` SET :FIELD = :DATA WHERE `id` = :ID');
$query->execute(array(
  ':FIELD' => $field,
  ':DATA' => $data,
  ':ID' => $id
));
$query->commit();

For more info Are there good tutorials on how to use PDO?

edited May 23 '17 at 12:13

Community

1
1

answered Dec 09 '13 at 10:50

Jurik

3,244
1
31
52

Sorry, but I was talking about using mysqli as I stated in the tags. But thanks – Denver William Dec 09 '13 at 10:52
This is just a little example that should show you the right direction. – Jurik Dec 09 '13 at 10:53
Oh - I just missed this little `i` - sorry for that. – Jurik Dec 09 '13 at 10:55
I am comfortable using mysqli and at this time, it is not worth learning an entirely new way to use mysql database. Thanks though – Denver William Dec 09 '13 at 11:00
I will take one thing from this, and that is `mysqli_prepare` and using it with `bind_...`. Thanks for the help! – Denver William Dec 09 '13 at 11:15

Code for Updating MySQLI PHP

3 Answers3