How to Escape Single Quotes in PHP for MySQL Insertion?

Escaping Single Quotes in PHP for MySQL Insertion

MySQL errors can arise when inserting data containing single quotes (') into a database. To resolve this issue, it's crucial to escape single quotes using appropriate techniques.

In your code snippets, you're using two INSERT queries to add form data to your database:

<code class="php">INSERT INTO job_log ...
VALUES
(...)</code>

<code class="php">INSERT INTO message_log ...
VALUES
(...)</code>

Query 1:

The first query works properly without escaping the single quote because you're likely using magic_quotes_gpc, which escapes strings from GET, POST, and COOKIE variables.

Query 2:

The second query fails when a string contains a single quote, such as "O'Brien". Unlike the first query, the string in the second query hasn't been escaped.

To prevent this issue, you should use mysql_real_escape_string() to escape strings before inserting them into the database. This will add backslashes () before single quotes:

<code class="php">INSERT INTO message_log ...
VALUES
(...)</code>

For example:

<code class="php">$escapedString = mysql_real_escape_string("O'Brien");
$query = mysql_query("INSERT INTO message_log ... VALUES ('$escapedString', ...");</code>

Escaping Strings:

It's good practice to always escape strings before inserting them into a database, even if you're not experiencing errors. This prevents SQL injection attacks and ensures data integrity. In PHP, you can use mysql_real_escape_string() or the PDO library's bindParam() method to escape strings effectively.

The above is the detailed content of How to Escape Single Quotes in PHP for MySQL Insertion?. For more information, please follow other related articles on the PHP Chinese website!