Inserting TEXT in mysql database showing error

Question

I have come to a weird problem. I have a text column in my table. I am inserting text taken from a textarea of html form into mysql database.

$sql="INSERT INTO mytable ( `username`, `firstname`, `lastname`,`about`,`email`) VALUES ('$username', '$fname', '$lname','$about','$email');";

about here is TEXT type column
And this is my textarea

<textarea id="oabout" name="about" rows="10" placeholder="Aboutyourself"></textarea>

If i insert the following text then the insertion works fine

I am a student

But if i try to insert the following text i am getting error

I'm a student

The error is shown as

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'm a student','[email protected]')' at line 1

How can i make the change so that the second text is also inserted into the database?

Community · Accepted Answer · 2023-11-21 15:10:29Z

1

the issue seems to be that your single quotes are not being escaped. In case you are using PHP this module will take care to escape everything that needs to be escaped in you input string: https://www.php.net/manual/en/pdo.quote.php

Untested example for your usecase, the $conn has to be you mysql connection string:

<?php
$conn = new PDO('sqlite:/home/lynn/music.sql3');
$about = 'I'm a student';
$quotedAbout = $conn->quote($about);
print $quotedAbout;
?>

Basically you just have to call the $conn->quote($someVariable) for every of your TEXT fields that you want to be automtaically escaped.

Best regards,

edited Nov 21, 2023 at 15:10

CommunityBot

11 silver badge

answered Jul 4, 2015 at 20:14

mat1010

8772 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Md Johirul Islam Over a year ago

Shouldn't this work $sql="INSERT INTO mytable ( username, firstname, lastname,about,email) VALUES ('$username', '$fname', '$lname',QUOTE('$about'),'$email');"; ? But it's not working too

Collectives™ on Stack Overflow

Inserting TEXT in mysql database showing error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related