how to post long text and insert into mysql database using PHP

Dear Experts,
I use PHP and MySql. My form is HTML5 form.
I post my form to another page to insert the form data inside mysql database.

I clean the form using this two functions.

function test_input($data) {
  $data = trim($data);
 $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}
 	
   $comment = test_input($_POST["comment"]);
   $comment = $mysqli->real_escape_string($comment);
   
   // I clean every input with these two functions ( function test_input and real_escape_string )

My sql sentence is below
$sql = "INSERT INTO tvmakale (konu,resim,makale,tarih) VALUES ('$konu','$resim','$comment','$tarih') ";

Open in new window


the sql output is below. Because of the quotation marks, my insert sentence is not working.

INSERT INTO tvmakale (konu,resim,makale,tarih) VALUES ('Dubai\'s New Airport','new-airpor.jpg','CNN) — With more people than ever flying, cities around the world are building new airports and upgrading old terminals to create facilities capable of handling tens of millions of passengers. The results are modern, stylish architectural statements that banish the dark, crowded travel spaces of the past. Here are 16 of the most exciting airport projects under construction or redevelopment. Istanbul New Airport Turkey has great ambitions to become a new meeting point between east and west. Its national carrier, Turkish Airlines, already flies to more countries than any other airline globally, and with the prosaically named Istanbul's New Airport opening soon, the country now has a gateway to be proud of. Replacing the older Atatürk Airport, it is a mega hub that will eventually be capable of handling 150 million passengers per year, perhaps even rivaling Dubai in terms of connecting travelers. The airport's official opening is scheduled for October 29, but the latest reports say it won't be fully operational until the end of 2018. The world's favorite airport and Asian mega-hub, Singapore Changi, is eagerly working towards opening its new Jewel and Terminal' 5. The Jewel is an impressive glass multi-use area connecting the existing terminals' and introducing shopping, eating and entertainment facilities for passengers and visitors to use. It will feature the world's tallest indoor waterfall, and is set to open in 2019. Terminal 5 will open to the east of the current terminals in the mid-2020s, with a capacity of "50 million" annual passengers. ','2018-10-25 12:58:12')

makale column data type is VarChar. I intend to insert here like 10.000 ( ten thousands characters )

what do you suggest I should do?
LVL 1
BRMarketingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
Use a Prepared Statement !

That approach will prevent the need to do the sanitizing that you're currently doing and you won't have to worry about quotes. It also protects you against SQL injection attacks.

$sql = "INSERT INTO tvmakale (konu,resim,makale,tarih) VALUES (?, ?, ?, ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_params("ssss", $konu, $resim, $makale, $tarih);

$konu = "whatever";
$resim = "some value";
$makale = (isset($_POST['comment'])) ? trim($_POST["comment"]) : "";
$tarih = "xxxxx";

$stmt->execute();

Open in new window

BRMarketingAuthor Commented:
Dear Christ,
I used your code and there is an error.
Fatal error: Uncaught Error: Call to undefined method mysqli_stmt::bind_params() in this sentence

$stmt->bind_params("ssss", $konu, $resim, $makale, $tarih);

what is "ssss" for?  
this line gives error... thank you.
Chris StanyonWebDevCommented:
Ahh sorry - typo. It should be bind_param()

The general idea is that you prepare a statement with placeholders instead of data using the question mark. You then bind those placeholders (parameters) to the data you actually want to use. When you bind those parameters, you tell the statement what the data type is : integer (i) / double (d) / string (s) etc. The first argument of the bind_param call specifies the datatypes of all the parameters, You have 4 parameters (question marks) in your query, so we have 4 's' characters - indicating that you're inserting 4 strings:

$stmt->bind_param("ssss", $konu, $resim, $makale, $tarih)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

BRMarketingAuthor Commented:
Dear Christ, thank you very much.
I learned something from you I should have learned long time ago.

One last thing, I use VarChar for the text 10000 characters,
should I use VarChar or text or diffrent data type for mySql database?
which one is better for the performance and data storage?
thank you
Chris StanyonWebDevCommented:
Go with VarChar. Data stored in Text columns is actually stored separately to the row data, so needs an additional disk read when requested. VarChar data is stored within the row, so the additional disk read is not needed.

And for future development - any time you're using data that's been submitted by the user (GET / POST) ALWAYS use prepared statements with parameters. Quite simply, it's just a whole lot safer than not using them.

And if you really want to start digging deeper, take a look at using PDO intead of MySQLi - they're both PHP libraries for accessing MySQL databases, but personally, I think the PDO implementation is just a lot cleaner and more intuitive.
BRMarketingAuthor Commented:
Dearr Chris Stanyon,
Thank you so much for your guidence. This is a perfect answer to my question.

you are great.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.