Which PDO parameter data type should I use for mysql blob?

how to read blob data in mysql
mysql blob data type
how to insert blob data in mysql using java
how to insert blob data in mysql using php
how to insert blob data in mysql workbench
mysql insert blob
mysql insert blob from file
how to read blob data in mysql using php

In one of my db tables I have a couple of columns storing encrypted text as BLOB.

So far in my prepared PDO queries i have been binding the values without specifying any PDO data type, something like this :

$sql->bindParam(':enc_txt', $enc_txt);

Everything works, but I was wondering if it would be better (and possible) to also specify the param type.

Should I use PDO::PARAM_STR ? Or maybe PDO::PARAM_LOB ?

Also, what happens when I don't specify any param type ? Does it default to PARAM_STR or it tries to detect the type automatically ?

Both should work. PDO::PARAM_LOB allows you to pass a stream but still works fine with a string. PDO::PARAM_STR does the job as well.

I personnaly use PDO::PARAM_STR.

More information here. It may depend on the way you are passing data, and your database's system. TL;DR : Oracle handles things slightly differently.

For your second question, a quick look at the doc here indicate PDO::PARAM_STR is the default. There is no type guessing.

Blob (Binary Large Object) Definition, , they can be used to store images or other multimedia files. If you're going to be using large chunks of data, as you have mentioned in your use-case, then yes -- I would use PDO::PARAM_LOB to manipulate your data using data streams. According to the PHP documentation: At some point in your application, you might find that you need to store "large" data in your database.

Well, everything could be answered from the documentation page

PDO::PARAM_LOB requires a file pointer resource while I doubt you are going to save your text into a file only to store it in the database.

The second question is also answerable: PDO::PARAM_STR is the default type when omitted.

What are the differences between the BLOB and TEXT datatypes in , values are treated as binary strings (byte strings). They have no character set, and sorting and comparison are based on the numeric values of the bytes in column values. TEXT values are treated as nonbinary strings (character strings). A Binary Large Object BLOB is a MySQL data type that can store binary data such as images, multimedia, and PDF files. In this guide, you will use the MySQL BLOB data type to store images with PHP on Ubuntu 18.04.

According to the documentation, the default $data_type is PDO::PARAM_STR:

So you should probably declare it to be PDO::PARAM_LOB for blob data.

Display all images stored as BLOB data from MySQL database , We will show you how to insert, update and select BLOB data. Let's see how PHP PDO handles the BLOB type in MySQL. The data column whose data type is the BLOB that is used to store the content of the file. Third, bind the file handle to the prepared statement using the bindParam() method and call the execute()  Sometimes, for the security reasons, you may need to store large data objects e.g., images, PDF files, and videos in the MySQL database. MySQL provides a BLOB type that can hold a large amount of data.

Can I store images in MySQL, textual or binary in nature. PDO allows you to work with this large data type by using the PDO::PARAM_LOB type code in your PDOStatement::bindParam() or  This is done by send_long_data(). The first parameter of this method indicates which parameter to associate the data with. Parameters are numbered beginning with 0. The second parameter of send_long_data() contains the actual data to be stored. While using send_long_data(), please make sure that the blob isn't bigger than MySQL's max_allowed

PHP MySQL BLOB: Insert, Update, And Select BLOB Data, -b option, mysql client, 611 b type code, data types, 738 back_log parameter, MySQL PDO, 572 Berkeley DB (BDB) storage engine, MySQL, 637 BIGINT datatype, 74 BLACKHOLE storage engine, MySQL, 640 BLOB datatype, MySQL, 647  Large objects can be either textual or binary in nature. PDO allows you to work with this large data type by using the PDO::PARAM_LOB type code in your PDOStatement::bindParam() or PDOStatement::bindColumn() calls. PDO::PARAM_LOB tells PDO to map the data as a stream, so that you can manipulate it using the PHP Streams API.

Large Objects (LOBs) - Manual, In this tutorial, we will show you how to write, read, and update BLOB data in Use the bindParam() method of the PDOStatement object to bind a parameter to a blob data into the documents table * @param type $pathToFile * @return type  If you use the BINARY attribute with a TEXT data type, the column is assigned the binary (_bin) collation of the column character set. LONG and LONG VARCHAR map to the MEDIUMTEXT data type. This is a compatibility feature. MySQL Connector/ODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.