1

I'm trying to implement this:

$sql = "INSERT INTO users (location) WHERE social_id='".$_SESSION["FBID"]."' VALUES ('".$_POST["location"]."')";

So the Location value comes from a form, however, the WHERE is needed obviously because it needs to go into the correct row for the user. This doesn't work, maybe someone knows if hyphens are misplaced?

ffritz
  • 2,180
  • 1
  • 28
  • 64
  • 1
    Are you sure you want an insert? does your users table only consist of a location ? I think you want an update don't you? You also shouldn't insert POST variables directly. First validate it's not some bad snippet trying to fetch records from your database. Also you should place your where at the end of the statement – Frnak Apr 24 '16 at 11:06
  • Thanks Frank, that was it. Yes I'm planning on checking that later, when it works. :) – ffritz Apr 24 '16 at 11:11
  • Start using mysqli_prepare statements. http://php.net/manual/en/mysqli.prepare.php – Sk_ Apr 24 '16 at 11:13

3 Answers3

2

you should have a look at this: MySQL Insert Where query

You don't want to insert, but update a record. You always insert a complete row - not a single column of an existing row. You use update for that. Therefore there is no need to use where when inserting.

Lookking at your problem it should be something like

UPDATE users SET location = $_POST["location"] where social_id = $_SESSION["FBID"]

As mentioned in the comment above you should, however still escape at least the location variable before inserting it.

Have a look at "mysql prepared statements"

Community
  • 1
  • 1
Frnak
  • 6,601
  • 5
  • 34
  • 67
  • Thanks for reply, it works. What is meant by "escape"? – ffritz Apr 24 '16 at 11:12
  • Means Escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection.Read this link http://php.net/manual/en/mysqli.real-escape-string.php – Sk_ Apr 24 '16 at 11:26
1

You should escape the string before inserting it into the database to prevent MySQL injection. Assigning it to a variable would be easier.

Also, I think you are trying to update the row, use UPDATE query instead of INSERT.

$FBID = mysqli_real_escape_string($conn, $_SESSION["FBID"])
$location = mysqli_real_escape_string($conn, $_POST["location"])

$sql = "UPDATE `users` SET `location` =  '$location' WHERE `social_id`='$FBID' ";
Panda
  • 6,955
  • 6
  • 40
  • 55
  • Thanks for this, I'm actually passing the variables to a function in another file. Should the escaping be done in the function then? Or in the if isset which calls the funtion? – ffritz Apr 24 '16 at 11:32
  • @FelixF. You're welcome! Both are fine, although escaping it before passing it into the function would be easier. – Panda Apr 24 '16 at 11:40
0

Please always prepare your query, never use a $_POST or any other user input value directly in your query to prevent SQL injection.

SQL Injection is more dangerous then you think

If you insert $_POST["location"] = "'' -- " into @Frank Provost's code, then you will UPDATE all the rows instead of the one with the FBID session key.

Multiple queries with SQL Injection

If you have multi query enabled then you can insert $_POST["location"] = "''; DROP TABLE users -- " into @Frank Provost's code, then you will DROP the table users.

Always use prepared statements

You can take a look at my PDO implementation example on GitHub: https://github.com/maartensch/database-pdo-mysql-class-php

Example code:

$sql = "INSERT INTO yourTablename(id,name) VALUES(:id,:name)";
$userInputId = 'yourUnescapedValue';
$userInputName = 'yourUnescapedValue';
$preparedStatements = array(':id'=>$userInputId,':name'=>$userInputName);
Db::getDb()->query($sql,$preparedStatements);
Neil Yoga Crypto
  • 615
  • 5
  • 16