0

Please note this is not a duplicated question, as all the other questions about this error, are related to reserved word that must be escaped, which is not this case.

I have a plain query, which is a concatenation of strings plus a file content using file_get_contents, the query will look like this:

CALL sp_student_booking_application_attachment_insert(
    'foreignkey code', -- varchar(255)
    'filename.pdf', -- varchar(255)
    'application/pdf', -- varchar(255)
    file content, -- longblob: not quoted
    file_size -- integer(11): not quoted
);

The store procedure is just a wrapper of an insert statement which looks exactly the same.

For same reason, if I upload a relative small file 10kb it works, however it won't work with any other bigger file, but I was also able to upload a PDF of 200kb, please note just a specific pdf, if I use another pdf even if smaller it won't work. The only error I get is: 

SQLSTATE[42000]: Syntax error or access violation: 1064 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 '?s(??\n??W?hcvӔ???.???a&??5+\"+w??    c??-?ƄqܷL,???-L?b?|)??K?71' at line 5

So I tried to quote the file content value using '' and using ``, but I still having the same issue. However when I try to upload a file trough phpmyadmin it upload even files with 2mb.

It may be useful, that this image/file is travelling trough a soap service on a json variable and file content is base64 encoded, which is then parsed as json on the soap server and the base64 is decoded. I have checked, and the file is the same size on both sides Soap client/soap server, so is not a php or soap related issue.

On my.conf max_allowed_packet is 996776960.

peterpeterson
  • 1,315
  • 2
  • 14
  • 38
  • 2
    Looks like you did not apply proper measures to escape/mask the data you are inserting into the query … so show how you are actually doing that. (Let’s assume you didn’t do so totally naive that you just concatenate the file content into the SQL statement without _any_ further handling, right …?) – CBroe Feb 04 '15 at 11:18
  • For example the basic escaping described here http://stackoverflow.com/a/7052685/1427878 is of course the absolute minimum you have to do … did you? – CBroe Feb 04 '15 at 11:22
  • @CBroe I have been looking at what you said, and basically it was a raw query a concatenation of strings, even dough I was using doing something like "'".addslashes($filecontent)."'", it wasn't enough so I tried to replace that raw, with the pdo prepare statement and it works. so if you post this as an answer I am going to give you the 200 :) – peterpeterson Feb 04 '15 at 23:21
  • Done that; plus a little more explanation of the problem and how prepared statements avoid it in the first place. – CBroe Feb 05 '15 at 09:02

1 Answers1

1

Looks like you did not apply proper measures to escape/mask the data you are inserting into the query.

Binary data that you read from a file is very likely to contain byte sequences that map to characters that have meaning in the SQL syntax, like for example a single quote ' – so those would mess up your syntax, if they are not handled correctly.

addslashes is not the function to use for that. Every database interface should provide a dedicated function/method for that; for the PHP mysql driver (which is depreacted) that would be mysql_real_escape_string, for mysqli it would be mysqli_real_escape_string resp. mysqli::real_escape_string. Those functions/method are especially designed for this specific purpose, and also take factors like the character set of the connection into account.

The other (and better) way is to use prepared statements in the first place. With those, the actual SQL “commands” and the data are send to the database separate from each other, so the case that the data messes up the SQL statement syntax can not even occur any more.

CBroe
  • 91,630
  • 14
  • 92
  • 150