php mysqli insert variables query [duplicate]

Question

This question already has an answer here:

• How can I prevent SQL injection in PHP? 28 answers

Ok so here is the question. I am trying to insert a variable into my query that is pre-defined. However it is not working. The query works if I just give it a value, but when I insert a variable into it, it fails. help?

$connection = new mysqli('localhost', 'user', 'pass', 'db'); 

$username = "test";

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

if ($result = $connection->query("INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES ('".$username."', 'test', 'test', 'test', 'test', 'test')")){

  echo "success";
  $result->close();

}
else {
  echo "error";
}

$connection->close();
?>

If I replace $username with any value, it works.. Am I missing something here?

Answer 1

Since ther was some discussion above i thought id provide the following examples in pdo and mysqli for comparison:

MySQLi:

$connection = new mysqli('localhost', 'user', 'pass', 'db'); 

$username = "test";

if ($connection->errno) {
    printf("Connect failed: %s\n", $connection->error);
    exit();
}

$username = 'test';

$stmt = $connection->prepare("INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES (?,'test', 'test', 'test', 'test', 'test')");

$stmt->bind_param('s', $username_value);
$username_value = $username; // not we could simply define $username_value = 'test' here

if ($result = $stmt->execute()){

  echo "success";
  $stmt->free_result();

}
else {
  echo "error";
}

$connection->close();

PDO:

try {

$db = new PDO($dsn, $user, $pass);
$username = 'test';

$stmt = $db->prepare("INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES (?,'test', 'test', 'test', 'test', 'test')");

$stmt->execute(array($username));

echo 'Success';
}
catch(PDOException $e)
{
  echo $e->getMessage();
}

Answer 2

In this case, looking at the context of your question it is better to assign the username variable with some data like $username=$_POST['username'];

This might help...otherwise avoid the double quotes and simply put down $username

Answer 3

The best answer to it is we must assign the variable we want into another variable. For example:

$username = $_POST['username'];

$a = $username;

mysqli_query("INSERT INTO tablename (username,test, test, test) VALUES ('$a', 'test', 'test');

Answer 4

Hello this is for anyone who might still need accomplish what was asked in original question.

A reason why someone possibly might want to not use prepared statements--from: http://www.php.net/manual/en/mysqli.quickstart.statements.php

"Using a prepared statement is not always the most efficient way of executing a statement. A prepared statement executed only once causes more client-server round-trips than a non-prepared statement."

//you will want to clean variables properly before inserting into db
$username = "MyName";
$password = "hashedPasswordc5Uj$3s";

$q = "INSERT INTO `users`(`username`, `password`) VALUES ('".$username."', '".$password."')";

if (!$dbc->query($q)) {
    echo "INSERT failed: (" . $dbc->errno . ") " . $dbc->error;
}    
echo "Newest user id = ",$dbc->insert_id;

Cheers!

Answer 5

Its been a long time and probably you've already found out the answer but just in case, it turns out that its actually a simple problem where you put Double quotes and dots in the mysqli query statement at VALUES('".$username"'), but if you just leave it in single quotes and just write the variable name inside the quotes like, VALUES('$username'), it will work. I think it applies for new versions of php though not sure i.e. Change

"INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES ('".$username."', 'test', 'test', 'test', 'test', 'test')"

to

"INSERT INTO users (username, password, email, firstName, lastName, createDate) VALUES ('$username', 'test', 'test', 'test', 'test', 'test')"

Notice in the VALUE field my variable is not enclosed in double quotes or concatenated in periods i.e. VALUES ('$username'), since it will save the periods as a value.

this works for me but I've noticed a problem in running the query with the same values again, it brings an error but it can be avoided by adding a column in your database table for an auto increment id to make sure that a value is being changed every time you run the query

Hope this helps