CodeIgniter Forums

Full Version: Insert binary value into table fails.
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hello,

I use CI as a base to develop a user registration system that stores some data into a SQL Server DB.
Password column datatype is set to binary(16). When I encrypt the password through my app and then try to insert the value into the DB table I get the error: Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query. It looks like that CI handles it as varchar and since the datatypes don't match, the error occurs.
Passwords are in this form 0x29E59C9B1ABD5F2DF024CCAC61FBDDDD . How can I handle and send the data to the DB as binary and not varchar?
I tried to change the datatype of the column to archer. It works, but the app doesn't read the data correctly.

Edit: If I create a stored procedure in SQL Server, can I send there data from CI or I'll have the same problem again?
I don't have much experince in using binary type data in DB. 
Once, I tried to keep IP addresses in binary format in DB, it caused me a lot of headache.
I worked on  MySql v5.3, as I remember it does not have auto conversion method to convert string to Binary, I converted string to binary in PHP  and stored them in DB.
This conversion caused some problem when insertion/update because of special characters that generated when converting to binary.
and then it also was to problem when comparing (searching  an IP) it with some value.

From that time I avoid using binary types in DB.

So I suggest you to avoid binary types Smile.
That's a HEX right? You need to use hex2bin before inserting it into your DB. And bin2hex to return it from the database.
I'm using BINARY(16) for storing UUID to get a faster JOINS. But why do you need to store password in BINARY?

PHP Code:
if ( ! function_exists('UUID_TO_BIN'))
{
    function 
UUID_TO_BIN($uuid) {
        
$uuid hex2bin(str_replace('-','',$uuid));
        return 
$uuid;
    }
}

if ( ! 
function_exists('BIN_TO_UUID'))
{
    function 
BIN_TO_UUID($uuid) {
        if( empty(
$uuid) ) return;
        
$uuid bin2hex($uuid);
        
$uuid substr($uuid08) . '-' substr($uuid84) . '-' substr($uuid124) . '-' substr($uuid164)  . '-' substr($uuid20);
        return 
$uuid;
    }

It's being read by an application made that way. I tried to change datatype to something else, but the 3rd party app can't read the password and won't let the user in. What i'm testing right now is running a stored procedure in SQL server through CI
I know it's late but i solved the issue above like this:

PHP Code:
public function auth($acc2,$pass,$a2,$a22) {
            //connect to mssql srv database
            $db2$this->load->database('srv',TRUE);

            $sql "INSERT INTO auth (account, password, q1, q2, a1, a2) 
            VALUES (
            '
$acc2', 
            CONVERT( BINARY(16), '
$pass', 1), 
            'PRU', 
            '_', 
            CONVERT( BINARY(32), '
$a2', 1), 
CONVERT( BINARY(32), '
$a22', 1))";
            $db2->query($sql);
            //close connection
            $db2->close();
 } 

Now, is this sql injection proof? I mean is CI sanitizes input?
It's not safe, you need to use Query Bindings:
https://codeigniter.com/user_guide/datab...y-bindings
Thank you for your answer!

That's what I thought. But even though this values are coming from $this->input->post("password", true)); ?
How can I bind the params with the CONVERT function?