CODEWITHSUNDEEP
phpmysqlforms
user13286
user13286

Reputation: 3075

Inserting special characters into SQL database from form

I am submitting form values into a database using PHP but I am running into an issue when user's enter special characters such as an apostrophe. For example if someone enters Bill's Pet Supply into organization, there will be an SQL error.

Here is my code:

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

if(isset($_POST['submit'])) {
    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];
    $email = $_POST['email'];
    $organization = $_POST['organization'];

    $sql = $conn->prepare("INSERT INTO submissions VALUES (:firstname, :lastname, :email, :organization)");

    $sql->bindValue(':firstname', $firstname);
    $sql->bindValue(':lastname', $lastname);
    $sql->bindValue(':email', $email);
    $sql->bindValue(':organization', $organization);

    $sql->execute();
}

$conn->close();

How can I change this code so that apostrophes and other special characters will be supported?

Upvotes: 0

Views: 825

Answers (1)

spencer7593
spencer7593

Reputation: 108410

Use prepared statements with bind placeholders. Both PDO and mysqli provide support for those.

Your SQL text would look like this:

$sql = "INSERT INTO submissions (firstname, lastname, email, organization)
VALUES (?, ?, ?, ?)";

If you are using mysqli

mysqli_prepare

myslqi_bind_param

myslqi_execute

$sth = $mysqli->prepare($sql);
if(!$sth) {
  // handle error
}
$sth->bind_param("ssss", $firstname, $lastname, $email, $organization);
if( $res = $sth->execute() ) {
  // process resultset
}

Similar functions available in PDO, but you can use "bind value" instead of "bind param".

If there's some reason you can't use prepared statements with bind placeholders, then at a minimum, you will need to properly escape any potentially unsafe values included in the SQL text.

If you are using mysqli, then generating the SQL text would look something like this:

$sql = "INSERT INTO submissions (firstname, lastname, email, organization)
VALUES ('" . $mysqli->real_escape_string( $firstname )
  . "', '" . $mysqli->real_escape_string( $lastname ) 
  . "', '" . $mysqli->real_escape_string( $email )
  . "', '" . $mysqli->real_escape_string( $organization )
  . "')";

But don't do that. Use a prepared statement with bind placeholders.

Upvotes: 1

Related Questions