CODEWITHSUNDEEP
phpdatabasemysql
Sisenando Calixto
Sisenando Calixto

Reputation: 23

How to insert large text into a DB with PHP?

I have a function that generates a random text with about 8,000 characters.

This text is generated at the time of first access the page, based on some parameters.

I would like this text to be storing in the database (not to be necessary to generate another text to each access).

I successfully stored in the database all the variables except the large text. Also could store when the text is small:

$LargeText=Texto(); //-> about 8,000 characters

if(empty($Descricao)){
$InserirDescricao = strtoupper($Nome);
mysql_query("UPDATE lavanderias SET descricao = \"$InserirDescricao\" WHERE id=$CidadeID");} //-> This works fine

if(empty($Texto)){
mysql_query("UPDATE lavanderias SET texto = \"$LargeText\" WHERE id=$CidadeID");} //-> This not works :(

if(empty($Texto)){
mysql_query("UPDATE lavanderias SET texto = \"ANYTHING\" WHERE id=$CidadeID");} //-> This works fine

The third message generates this error message:

Fatal error: Uncaught exception 'mysqli_sql_exception' with message 
'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 
'servico"><div class="servico-ilustracao servico-ilustracao-5"><h2>Lavagem de Pro' 
at line 1'

It is possible that the number of characters is preventing the insertion? How do I manage to insert the variable with the large text?

Upvotes: 1

Views: 2138

Answers (2)

rray
rray

Reputation: 2556

Avoid problems with SQL injection using prepared statements. The syntax error is caused by this single quoted 'servico">.

It's simple to use prepared statements: just change the values for question marks, prepare the SQL instruction, make the bind si mean the type of data s => string, i => integer, d => double/float and b => blobs and finally excute() tries to apply the update on the database.

$mysqli = new mysqli('localhost', 'user', 'pass', 'database'); 

$sql = "UPDATE lavanderias SET texto = ? WHERE id = ?)";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('si', $LargeText, $CidadeID);
if(!$stmt->execute()){
   echo mysqli_error($mysqli);
}

Upvotes: 2

Philipp
Philipp

Reputation: 69773

According to your SQL error message which you posted as a comment there is at least one single-quote in the text you are trying to update. Because you create your SQL queries through string concatenation, that single quote is interpreted as part of the SQL syntax and leads to a syntax error.

Always run any strings you put into an SQL query through the function mysql_real_escape_string to escape any characters which have a special meaning in SQL. Even better, dump the old, obsolete mysql_* API and use the newer mysqli-API with prepared statements. This API does that automatically for you.

This is very important because your query is also vulnerable to SQL injections. Imagine what happens when I put the text '; DROP TABLE lavanderias; -- on my website.

Upvotes: 5

Related Questions