' <--- in mysql text don't get inserted in database

Question

Hello i'm a beginner so please at least try to give me a hint,a example.

English isn't my main language so please endure it.

If somebody type " Hello my name is J'hon ' the text don't insert in database, but if he type 'Hello my name is jhon' it does. I think it is something about '

Ok so i'm having the problem that if someone types

'Hello my name is J[color=#FF0000]'[/color]hon J'onz. ' is not inserted in the database..

This is the script:

mysqli_query($DB_H, "INSERT INTO tickets (name, continutscurt, continut,type,status) VALUES ('".$_SESSION['username']."', '".$_POST['titlu']."', '".$_POST['continut']."', $numar, 0)");

Instead of using all of those $_ variables and having to deal with the single/double quote problems - why not assign them to variables FIRST and then use the temporary variables? Like $username, $tifu, etc... Also, don't forget about SQL injections. So either bin2hex and unhex your values or use one of the checkers provided for MySQL. (https://en.wikipedia.org/wiki/SQL_injection) — Mark Manning, Mar 10 '16 at 20:56
@MarkManning Great comment! My answer explained the single/double quote problem and the issue with unhexing the values. It's easier to use established CSS practices. — , Mar 10 '16 at 21:00
Please give me a website to learn more about this! I don't understand what do you mean ? i want to learn how to create a safe-insert text — State Valentin, Mar 10 '16 at 21:12
I suggest you learn how to use Prepared Statements for your SQL calls instead of simply joining the strings together. It's a much better technique for many reasons. — Spudley, Mar 10 '16 at 21:25
[Your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). — Jay Blanchard, Mar 10 '16 at 22:34
@StateValentin: I recommend you check out the "Open Web Application Security Project". [**SQL Injection Prevention Cheat Sheet**](https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet). — spencer7593, Mar 10 '16 at 23:55

score 4 · Answer 1 · edited May 23 '17 at 12:07

You should really use prepared statements when dealing with any kind of user-input. If you for any weird reason isn't using prepared statements, take a look at the function mysqli::real_escape_string. This will deal with special characters, such as ', which may break the SQL.

With using prepared statements, your code would look like

if ($stmt = $DB_H->prepare("INSERT INTO tickets (`name`, continutscurt, continut, `type`, `status`) VALUES (?, ?, ?, ?, ?)")) {
    $stmt->bind_param("ssssi", $_SESSION['username'], $_POST['titlu'], $_POST['continut'], $numar, 0);
    $stmt->execute();
    $stmt->close();
} else {
    echo mysqli_error($DB_H);
}

If you however want to use mysqli::real_escape_string, you'll need to bind the SESSIONs and POSTs to a variable where in you insert instead, like this (you can also do it directly in the query, but this makes for cleaner code).

$username = mysqli_real_escape_string ($DB_H, $_SESSION['username']); 
$titlu = mysqli_real_escape_string ($DB_H, $_POST['titlu']); 
$continut = mysqli_real_escape_string ($DB_H, $_POST['continut']); 
$numar = mysqli_real_escape_string ($DB_H, $numar);

if (!mysqli_query($DB_H, "INSERT INTO tickets (`name`, continutscurt, continut, `type`, `status`) VALUES ('$username', '$titlu', '$continut', '$numar', 0")) { 
    echo mysqli_error($DB_H); 
}

I also put backticks ` around name, status and type, as these are keywords in SQL. This isn't strictly necessary, but it's good practice with words that are listed as either reserved words or keywords, more info on this list of keywords.

You shouldn't take for granted that your queries are successful, so I added an if-block around them. Errors shouldn't be displayed unless in production/development.

References:

I removed my quibble based on the edit. If we use backticks, we don't have to fiddle with worrying about whether `name` is a *reserved word* or just a keyword. Just wrap it in backticks, and the problem avoided. +10 Good answer. — spencer7593, Mar 10 '16 at 21:42

score 2 · Answer 2 · answered Mar 10 '16 at 21:13

The issue is SQL Injection.

You have potentially unsafe values being included within the SQL text.

To see this, break up the code a little bit.

  $sql = "INSERT INTO tickets ...'" . $val . "' ... ";
  echo $sql;

The echo is there just as a way to see what's going on, for you to examine the contents of the string containing the SQL text. And then take that string over to another client, and test it. And you will see what the the problem is.

   ... VALUES ( ..., 'J'onz. ', ...

isn't valid. That single quote is ending the string, so the string is just 'J', and the next part, MySQL is going to try to interpret as part of the SQL, not the string value. (This is a nefarious vulnerability. Cleverly constructed strings and wreak havoc on your application and your database.)

One approach to fixing that is to sanitize the values, so they can be safely included.

   ... VALUES ( ..., 'J\'onz. ', ...
                       ^^

   ... VALUES ( ..., 'J''onz. ', ...
                       ^^

As a simple demonstration try these queries:

 SELECT 'J\'onz. '
 SELECT 'J''onz. '
 SELECT 'J'onz. '

(The first two will return the string you expect, and the third will cause an error.)

The take away is that potentially unsafe values that are going to included in the text of a SQL statement need to be properly escaped. Fortunately, the MySQL client library includes mysqli_real_escape_string function. Variables that may potentially contain a single quote character can be run through that function, and the return from the function can be included in the SQL text.

  $sql = "INSERT INTO tickets ...'" 
         . mysqli_real_escape_string($DB_H,$val)
         . "' ... ";

Again, echo out the $sql and you can see that a single quote has been escaped, either by preceding it with a backslash character, or replacing it with two sinqle quotes.

There's a much better pattern than "escaping" strings. And that's to use prepared statements with bind placeholders.

The SQL text can be a static string:

$sql = 'INSERT INTO mytable (mycol) VALUES ( ? )'

And then you msyqli_prepare the statement.

And then supply values for the placeholders with a call to mysqli_bind_param.

And then call mysqli_execute.

With this pattern, we don't need to mess with running the "escape string" function to sanitize the inputs.

Awesome answer Spencer! One problem, you can still INSERT data with SQL even if it's not sanitized or secure? It may be more safe, but this isn't the solution... — , Mar 10 '16 at 21:16
@ProgrammingTree: I'm sure there's more than one problem. If we need to store "O'Reilly" as a value, certainly, we need to allow that. Certainly, it's possible for custom code to perform additional checking of allowable values. But in terms of the approaches I outlined ( real_escape_string, and prepared statements with bind placeholders), these *are* generally accepted as the right solutions. — spencer7593, Mar 10 '16 at 21:24
A very thorough answer, and it answers this question as well. I don't see why it was downvoted? Upvoted it! — Qirel, Mar 10 '16 at 21:28

' <--- in mysql text don't get inserted in database

2 Answers2