views:

279

answers:

4

Hi guys and girls,

I am attempting to upload a .pdf file into a mysql database using php.

It is all good except for the contents of the file. No matter how I seem try to escape special characters, the query always fails, mostly with "Unknown Command \n".

I have used addslashes, mysql_real_escape_string, removeslashes etc.

Does anyone have any ideas on how to escape file contents?

Many Thanks,

+2  A: 

I don't see why you would want to store a file in a database, but I suggest you take a look at prepared statements.

Franz
+1 Same thing for mysqli: http://www.php.net/manual/en/mysqli.prepare.php
Daren Schwenke
Thanks, Daren. Good idea.
Franz
This worked perfectly, thanks a lot. I'm storing it in the database for a few reasons, can't be executed, database compression, database backup makes a backup of the file etc not sure if these are valids points as just getting started with mysql.Thanks alot
Michael
*"can't be executed", "database compression", "database backup makes a backup of the file"*. Each of these can be achieved with storage in the filesystem. Maybe when you reach the "50% of app is running" stage, you might want to give time to moving binary files to filesystem storage.
namespaceform
That is very true, what got me thinking, was which is better? In the database its a backup script, different server, mirroring and all that. Surely in terms of easiest to setup this option is more deploy and go? Not sure about performance though. Although the internet will be most people's bottle neck either way I guess. Are there any negative affects of storing files inside databases?
Michael
True, performance would be another downside of the database solution.
Franz
A: 

Guess: You may be encountering errors due to the incompatibility between character sets. PDF is probably a binary file so you need to make sure that db column is set up to handle it that.

Joe Philllips
Yeah I tried blob, large / big blob and binary. Escaping it seemed to be the problem.
Michael
A: 

Beside the escaping problem you might run into "packet too large" errors if the (MySQL) system variable max_allowed_packet is set to a "small" value.
Using the mysqli extension, prepared statements and mysqli_stmt::send_long_data you can avoid both problems.

VolkerK
I wil have to look into this, hopefully as it is restricted to small files this shouldn't occur. I can always set it to a bigger packet.
Michael
+1  A: 

Hi, I've used the following sequence before, which seems to work nicely, and will store any data into the db, including images, pdfs, arrays of data, etc... :)

Storing the data (can be a string, array, object, etc.);

First, turn the data into a base64 encoded string

$strData = strtr(
             base64_encode(
               addslashes(
                 gzcompress( serialize($dataToStore) , 9)
                 )
               ) , '+/=', '-_,');

Then store that string data in the db...


Retrieving the data;

Extract the string data from the db

decode the data back to what you want (you may need to perform an extra step after this depending on the input data, array, image, etc.)

$returnData = unserialize(
                gzuncompress(
                  stripslashes(
                    base64_decode(
                      strtr($strDataFromDb, '-_,', '+/=')
                    )
                  )
                )
              );

This certainly helped me to store what I needed to store in a mySQL db!

Dave Rix
Just to be clear... [after reading the above I realised I'd had a bit too much wine :)]The db field you want to store the above data in can just be a plain text field, the base64 encoding turns the output into standard text characters.I'm not sure where exactly I found this code, so I can't take full credit for it, but it does exactly what I required of it...
Dave Rix
I used a combination of this technique and the prepared statement technique, works wonders and solved ll my problems! Thanks a lot!
Michael