Issue with the character: ' (single quotation) when trying to save to mySql

Question

When ever there is this character in a string I try to save: ' (single quotation) - it does not save. No error is generated. I need help to understand what is wrong. My character set must accomodate French characters. Otherwise everything works well.

<?php
header ('Content-type: text/html; charset=utf-8');

include_once('../../../init.php');

mysql_set_charset("utf8");

$articleid  = $_POST['articleid'];
$contenu  = $_POST['editabledata'];
$name  = $_POST['name'];

mysql_query("
UPDATE al_articles SET $name='$contenu'
WHERE (ArticleID='$articleid')

") or die(mysql_error());

?>

You should be using PDO / MySQLi with prepared statements. The error you're getting is for exactly the same reason as why you're vulnerable to SQL injections attacks, the `'` is ending your column value prematurely since you've closed the string causing an SQL syntax error. — Jonnix, Jun 09 '16 at 10:48
Please dont use [the `mysql_` database extension](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the `PDO` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) its really pretty easy — RiggsFolly, Jun 09 '16 at 10:49
you should escape single quotes. `mysql_real_escape_string($content)` will do. *Note* mysql library is deprecated and should not be used. Used PDO or mysqli which can use prepared statement — undefined_variable, Jun 09 '16 at 10:49
Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared statement and parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jun 09 '16 at 10:50
mysql_real_escape_string($content) <-- this works, thanks! I got all your comments, I will try to improve my skills! — Sergelie, Jun 09 '16 at 10:54

score 1 · Answer 1 · answered Jun 09 '16 at 10:54

What are you doing is called SQL Injection because you are using raw user input in your query.

So if you pass Hello, my name is 'justinas' than your query looks like UPDATE al_articles SET $name='Hello, my name is 'justinas''. And as you see it ruins your SQL query because if closed string.

How to solve it

Use non-deprecated library: PDO or mysqli_*.
Than use prepared statement and parameters bindings:

$name = preg_replace('/[^a-zA-Z]/', '', $_POST['name']);
$sql = $pdo->prepare("UPDATE al_articles SET `{$name}` = :content WHERE `ArticleID` = :id");
$sql->execute([
    ':content' => $_POST['editabledata'],
    ':id' => $_POST['articleid']
]);

I understand. This solution worked: mysql_real_escape_string($contenu). Your do not, It simply refuses to save. Since I am not an expert, I do not know why! But I got the point. Thank you. — Sergelie, Jun 09 '16 at 11:08

score 0 · Accepted Answer · answered Jun 09 '16 at 10:52

0

Use addslashes

$articleid  = addslashes($_POST['articleid']);
$contenu  = addslashes($_POST['editabledata']);
$name  = addslashes($_POST['name']);

answered Jun 09 '16 at 10:52

Mani

2,675
2
20
42

Issue with the character: ' (single quotation) when trying to save to mySql

2 Answers2