1

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?

4
  • 3
    Question would belong on StackOverflow (will most likely be migrated). 8000 chars isn't that long. If the fields aredefined properly there shouldn't be a problem. What error messages do you get? They should tell you the problem. Maybe try to output the SQL and manually run it from mysql command line client. Commented Apr 8, 2016 at 10:20
  • 2
    You should consider switching to the newer mysqli API and use prepared statements. It is much easier to avoid security vulnerabilities with it.
    – Philipp
    Commented Apr 8, 2016 at 10:22
  • I got this: 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' Commented Apr 8, 2016 at 11:21
  • 5
    This has little to do with the size of the text, your query is borked. There's probably an unescaped quote in your text that breaks it. Switching to prepared statements, as @Philipp already suggested, will take care of this.
    – yannis
    Commented Apr 8, 2016 at 11:46

2 Answers 2

5

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.

0
2

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);
}
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.