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
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...)
You need to replace the single-quote with 2 single-quotes, like this:
str_replace("'", "\''", $bandname);
before sending the string to SQL. It will then show in the SQL table as a single-quote.
This should do the trick:
$bandname = mysqli_real_escape_string($_POST['bandname']);
$genre = mysqli_real_escape_string($_POST['genre']);
$location = mysqli_real_escape_string($_POST['location']);
mysqli_query($uys, "INSERT INTO users SET bandname='$bandname', genre='$genre', location='$location', bio='$bio', password='$password', email='$email', ip='$ip'");`
You need to escape your strings, otherwise it's possible to attack your database with sql injection.