allow special characters in MySQL

Question

I need to allow special characters including ' in MySQL. I have a "bio" text field which allows user data and I am using the POST method. My PHP is included:

    $uid = $_POST["UID"];
    $bio = $_POST["bio"];

    $update_query = "UPDATE `User` SET bio = '$bio' WHERE uid = '$uid';";
    $conn->query( $update_query );
    $conn->close()

All the users are verified so there should be no danger in allowing them to use special characters. How can I update my code?

@rtfm I'd suggest using prepared statements over linking an answer that escapes input ;-) — Qirel, Jul 12 '17 at 22:50
Using prepared statements will properly escape the special characters and help prevent SQL Injection (as mentioned by others) — Sloan Thrasher, Jul 12 '17 at 22:52

score 2 · Accepted Answer · answered Jul 12 '17 at 23:13

2

You can escape the string by using the mysqli_real_escape_string() function. Here is some Documentation. Basically, you pass in a connection and a string, and it returns a safe string to input into your database.

In your case you would do something like this:

$uid = mysqli_real_escape_string($conn, $_POST["UID"]);
$bio = mysqli_real_escape_string($conn, $_POST["bio"]);

ps: you should always use mysqli in php

answered Jul 12 '17 at 23:13

JoseRivas1998

433
1
6
14

Thank you, I finally got it to work – Sudacris Jul 12 '17 at 23:33
1

A better approach is to use prepared statements with placeholders in the query. This is always better than escaping. You also said that you should always use mysqli (as OP is using), that's fine to use (as long as you prepare and bind!!), but pdo is just as valid as mysqli. There's not necessarily a right or wrong between those. – Qirel Jul 16 '17 at 08:51

score 1 · Answer 2 · answered Jul 12 '17 at 23:09

@Alex Tartan is absolutely correct, you should be using prepared statements. Not only do they solve this problem completely, but they go a long way toward protecting you from malicious users. Here's an example:

$host = 'localhost';
$db = 'my_database';
$user = 'my_username';
$pass = 'my_password';
$charset = 'utf8';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE               => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES      => false
];
$pdo = new PDO($dsn, $user, $pass, $opt);

$stmt=$pdo->prepare("UPDATE `User` SET bio = :bio WHERE uid = :uid");
$stmt->bindParam('bio', $_POST['bio']);
$stmt->bindParam('uid', $_POST['UID']);
$stmt->execute();

The reason this is so valuable is the variable values (e.g., the contents of $_POST['bio']) are sent to the database seperate from the SQL query. Thus, they need no special treatment and may contain pretty much anything your character set allows.

allow special characters in MySQL

2 Answers2