php: how to insert large form data into mysql

Question

I am trying to insert a data from a form which has about 1990 characters into mysql. How ever the insert is not working. when i var_damp the content of the variable is shows the correct content. When i set it to an empty string the insert works. I have done my research and still can't get ti to work. I am not trying to upload a file. This characters are from a textarea in my form.

Below is the insert code:

if (isset($_POST['val'])) {
    $score = $_POST['val'];
    $course = $_POST['course'];
    $mysqli->query("INSERT INTO `evaluate` (`id`, `course`, `score`) VALUES (Null, '$course', '$score')");

Note: is score column has type TEXT in the database.

Your code is vulnerable to SQL injection and in this case you may actually be SQL injecting yourself accidentally. Does your long string of text happen to contain a single quote (`'`) character? — Chris, Apr 23 '16 at 22:59
why don't you escape the long string? http://php.net/manual/es/mysqli.real-escape-string.php — mr_sudaca, Apr 23 '16 at 23:03
Also worth noting that you should leave `id` out of your `INSERT` statement if you want it to auto-assign. That's the easiest way. — tadman, Apr 23 '16 at 23:56

score 1 · Accepted Answer · edited May 23 '17 at 11:54

This is a common problem because most introductions to mysqli don't cover it right away even when it should be the first thing you learn. Inserting into any database, especially SQL, requires carefully escaping the values you're supplying. The way these are escaped varies depending on the platform, but the good news is that mysqli can handle it for you.

The key is using prepared statements:

$stmt = $mysqli->prepare("INSERT INTO evaluate (course, score) VALUES (?,?)");
$stmt->bind_param('ss', $_POST['course'], $_POST['val']);
$stmt->execute();

Now it's best to enable exceptions so that any errors are not ignored. Once in a while we all make little mistakes that can be a giant pain to track down if there isn't any warning about them. Exceptions make a lot of noise.

If you're just getting started with PHP and databases, you might want to evaluate using PDO which is significantly better than mysqli for a number of reasons, or a higher level database layer like Doctrine or Propel which make using the database a lot more pleasant.

score 0 · Answer 2 · answered Apr 23 '16 at 23:15

0

I have a single quote (') in the text and not escaping it meant that the SQL statement was been interpreted wrongly

The correct way to go, and you must always do this, is:

$score = $mysqli->real_escape_string($_POST['val']); $course = $mysqli->real_escape_string($_POST['course']); $mysqli->query("INSERT INTOevaluate(id,course,score)VALUES (Null, '$course', '$score')");

answered Apr 23 '16 at 23:15

spirit

283
2
16

1

The best way to do this is with [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php), not by manually escaping. Things like `$score` should never appear in a query since all it takes is one mistake and it's vulnerable. Fix this up by using placeholders and you've permanently solved the problem. – tadman Apr 23 '16 at 23:53

php: how to insert large form data into mysql

2 Answers2