How to Insert a Value That Might Contain an Apostrophe

Question

How would I go about writing a SQL statement that would insert values that might contain an apostrophe (for example one person's last name was Conner and another's was O'Conner)? After some searching, I found examples using a double apostrophe (O''Conner example) but each example had the string hard coded in the the INSERT. I haven't run across any examples where the value may or may not contain an apostrophe.

My simple statement doesn't have any issues when no apostrophe is used but when one is it fails. I know I could replace the apostrophe using str_replace but, obviously, that would cause the O'Conner example to be displayed as OConner.

Here is a shorthand version, just for an example:

page1.php

// PHP
include_once('phpdata.php');
if (isset($_POST['firstname']) && isset($_POST['lastname'])) {
    $firstname = $_POST['firstname'];
    $lastname = $_POST['lastname'];

    // SQL connection
    $insert = doInsert($firstname, $lastname);
    // Execute statement using odbc_exec, etc.
}

// HTML
<input type="text" class="required" name="firstname" id="firstname" />
<input type="text" class="required" name="lastname" id="lastname" />

phpdata.php

function doInsert($firstname, $lastname) {
    $insert = "INSERT INTO mytable (firstname, lastname)
                VALUES ('$firstname', '$lastname')";
    return $insert;
}

Consider using [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement). — Marty McVry, Sep 20 '13 at 20:31
I accidentally left out the last closed quote in the INSERT but keep getting an error when I try to edit the post. Not sure why I can't edit it. — Brian, Sep 20 '13 at 20:33
There's probably hundreds of questions like this by now on Stack Overflow. [Here's a very popular one from the Related questions sidebar](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php?rq=1) — Izkata, Sep 20 '13 at 20:43

Fouad Fodail · Accepted Answer · 2013-09-20T21:01:23.400

4

Using PDO with prepared statements will take care of escaping your inputs :

$dsn  = "mysql:host=localhost;dbname=your_db_name";
try {
    $db = new PDO($dsn, 'your_username', 'your_pass');
 } catch (PDOException $e) {
    die( "Erreur ! : " . $e->getMessage() );
 }

 $query  = "INSERT INTO mytable (firstname, lastname)
            VALUES (:firstname', :lastname)";
 $stmt   = $db->prepare($query);                          
 $stmt->bindParam(':firstname', $firstname);
 $stmt->bindParam(':lastname', $lastname);
 $stmt->execute();

Doc : PHP Data Objects

edited Sep 20 '13 at 21:01

answered Sep 20 '13 at 20:35

Fouad Fodail

2,653
1
16
16

Now use ODBC... :-) (See OP) – Marty McVry Sep 20 '13 at 20:39
1

Topic starter don't know how prepare variables for safe SQL. You recommended PDO... This is like shot to small birds from tank. – newman Sep 20 '13 at 20:39
@newman I think he should start from the right way. simplest things are not always best things. – Fouad Fodail Sep 20 '13 at 20:46
Yes this is right way. But in some cases novice programmers just copy and past snippet and don't understand how it works, because all code is very hard. For education good start from simple ways and then understand all risks. – newman Sep 20 '13 at 20:52
2

@newman: I definitely don't want to turn this into a discussion regarding what's best for novice programmers, but in my programming course, I teach my students to use prepared statements from day 1! (Be it a simple example...) And I do explain the risks involved when not using prepared statements. – Marty McVry Sep 20 '13 at 20:58

score 2 · Answer 2 · answered Sep 20 '13 at 20:39

You could use replace to replace all ' with ''. However, the proper way to do it is use parameterized queries where you pass your value to insert to the SQL Statement as a parameter. Then the language can clean up ' and any other characters/keywords that could cause an issue. No matter the language, parameterized queries are the way to go.

score 2 · Answer 3 · answered Sep 20 '13 at 20:49

Consider using prepared statements. It's the best way to input user submitted data into a database. It makes sure the data is properly escaped automatically!

phpdata.php

<?php

function doInsert($firstname, $lastname) {
    $insert = "INSERT INTO mytable (firstname, lastname)
                VALUES (?, ?)";
    $pstmt = odbc_prepare($odb_con, $insert); /* Use global $odb_con to access the connection */
    $res = odbc_execute($pstmt, array($firstname, $lastname));
    return $res; /* Should return TRUE on success. */
}

?>

Do note that I haven't included any error checking in my code. Might be wise to implement that as well.

Good luck!

Looks almost identical to what I came up with from @FouadFodail answer (with ODBC). As for the error checking, yes I definitely do. I just tried to make a quick simple example to keep it short and sweet. Thanks again! — Brian, Sep 20 '13 at 20:53

score -4 · Answer 4 · answered Sep 20 '13 at 20:35

-4

You can try use function addslashes for prepare data before insert.

I don't know how your work with database but this function is simple way for ask to your question.

AddSlashes

answered Sep 20 '13 at 20:35

newman

2,689
15
23

1

-1 Please don't suggest using addslashes. It isn't the right function for escaping characters for SQL. – Bill Karwin Sep 20 '13 at 20:36
This is simple way for novice. – newman Sep 20 '13 at 20:38
1

@newman It's also wrong to the point of dangerous, as it creates a false sense of security. – Izkata Sep 20 '13 at 20:41

How to Insert a Value That Might Contain an Apostrophe

4 Answers4