用户在输入/文本区域中使用单引号时未处理的数据

Question

I wasn't too sure on how to word the title, so I'll try my best to explain here.

I've created a register system and came across a bug that I never took into consideration when developing the system. When a user types in the input boxes or a textarea, if they use single quotations the data won't be sent to the database as it will be closing the query.

This is my query code:

mysqli_query($uys, "INSERT INTO users SET bandname='$bandname', genre='$genre', location='$location', bio='$bio', password='$password', email='$email', ip='$ip'"); 

Of course if they don't use single quotations, there will be no error. They can use double quotes fine.

My variables are like this:

$bandname = $_POST['bandname'];
$genre = $_POST['genre'];
$location = $_POST['location'];

What is a way around this? I'm not the best with PHP, still learning so your help will be amazing and will help me lots.

Sorry if this wasn't well explained, if you're confused on what I mean I'll try my best to explain it better

Answer 1

This is a serious issue. What you face here is a wide open SQL INJECTION

You concatenate a query from unsanitized strings - this might lead to any kinds of troubles, where the smallest is getting your whole database deleted...

Don't concatenate query strings without using proper sanitization! In this case, mysqli_real_escape_string is the proper solution.

The most recommendable (is that a word?) solution is using prepared statements wherever possible:

$stmt = $mysqli->prepare("INSERT INTO users SET bandname=?, genre=?, location=?, bio=?, password=?, email=?, ip=?");

$stmt->bind_param("sssssss", $bandname, $genre, $location, $bio, $password, $email, $ip);
$stmt->execute();

Note: sanitization is still important from a content point of view, to prevent issues like XSS attacks, or Javascript injection to pages...

(Also, using PDO promises independence of databases too, it is worth checking it out...)