Problem with SQL query with variable that calls by php

Question

I wrote a SQL query for checking name in php, but it does not work.

I have no assumptions how to fix it, but I assume it's just mistake in syntax.

$username = $_POST["username"];

$nameCheckQuery = "SELECT username FROM users WHERE username '" . $username . "';";
$nameCheck = mysqli_query($db, $nameCheckQuery) or die("2: Name check query failed");

I receive error log on query.

what is the error? also, perhaps its better to learn about [prepared statement](https://stackoverflow.com/questions/24988867/when-should-i-use-prepared-statements/24989031).. — Bagus Tesa, Apr 04 '19 at 07:38
`WHERE username '"` is missing a `=`, should be `WHERE username = '"` — brombeer, Apr 04 '19 at 07:38
Please include the exact, complete errors you receive in the question. (That said, you're missing a ` = ` after `WHERE username`). Without exact errors, you're relying on others to be able to guess what's wrong — Avery, Apr 04 '19 at 07:39

score 4 · Accepted Answer · answered Apr 04 '19 at 07:48

The reason it's failing is likely due to you missing a = after username.

This code is open to SQL injection and you should use prepared statements.

The most basic of a prepared statement looks something like this:

$stmt = $db->prepare("SELECT * FROM users WHERE username = ?");

$username = $_POST['username'];

$stmt->bind_param('s', $username);

$result = $stmt->execute();

score 1 · Answer 2 · edited Jun 08 '19 at 10:44

The main problem of your query is that you forget to insert = next to WHERE username.

You have to write:

$nameCheckQuery = "SELECT username FROM users WHERE username ='" . $username . "';";

Right now it works but......

The query you are using is not preventing a SQL INJECTION attack (one of the most used attack against database).

Please take a look at the ways you can connect to the database:

use PDO (it works with 12 database type);
use MSQLI (it works only with MYSQL database and you are using it);

In other word, if you are planning that you will move your application in another database type please consider to use PDO, instead.

Using PDO preventing SQL injection you have to prepare the SQL statement like this:

$stmt = $pdo->prepare("SELECT username FROM users WHERE username = ?");
$stmt->execute([$_POST['username']]);
$arr = $stmt->fetch();

score 0 · Answer 3 · edited Jun 08 '19 at 10:43

For Starter, please use this escape string:

$username = $mysqli->real_escape_string($_POST["username"]);

Simply do it like this and don't get confused with quotes. You can still print php variables inside single quote like this.

$nameCheckQuery = "SELECT username FROM users WHERE username = '$username'";

or to edit your code, this is how you can achieve it.

$nameCheckQuery = "SELECT username FROM users WHERE username ='" . $username."'";

Just to answer your question, it is Vulnerable to Sql Injection.

Reasons why Sql Injection occurs: SQL Injection occurs when an attacker is able to send their own instructions to your database and the database executes those instructions. This occurs when a PHP developer has taken input from a website visitor and passed it to the database without checking to see if it contains anything malicious or bothering to clean out any malicious code.

SQL Injection can allow an attacker to access all of your website data. They can also create new data in your database which may include links to malicious or spam websites. An attacker may also be able to use SQL Injection to create a new administrative level user account which they can then use to sign-into your website and gain full access.

SQLi is a serious vulnerability because it is easy to exploit and often grants full access immediately.

This is how you can achieve it, which provides detailed functionality. https://stackoverflow.com/a/60496/6662773

I deleted my comments, because they don't make much sense anymore. — KIKO Software, Apr 04 '19 at 07:59
Why do you recommend using `real_escape_string` and then provide a link to prepared statements? Why not use them in the first place? — Nico Haase, Apr 04 '19 at 08:26

Problem with SQL query with variable that calls by php

3 Answers3