testing database with binary type in mysql using phpunit

Question

I try to write tests using php for database. I have a table that stores hash of password as binary. How can I set test data in xml dataset, for example here is hex of my data and I get an error data too long to column. Thank you.

 <dataset>
    <table name="subscription_ips">

     <column>password</column>   
     <row>
       <value>0x771C87E79B130E3FB966E424D7F1358D8DABBA0A26F288C0C1B5D8E3D95F2942100CA54B6824A7AC0964180A9426A1C37C371BA3FDBB000621FE175608C4B16C</value>
     </row>

 </table>

</dataset>

But it looks like phpunit can't insert it, using

0x771C87E79B130E3FB966E424D7F1358D8DABBA0A26F288C0C1B5D8E3D95F2942100CA54B6824A7AC0964180A9426A1C37C371BA3FDBB000621FE175608C4B16C instead of 0x771C87E79B130E3FB966E424D7F1358D8DABBA0A26F288C0C1B5D8E3D95F2942100CA54B6824A7AC0964180A9426A1C37C371BA3FDBB000621FE175608C4B16C trying to insert test data to table.

binary just work as `char` as mentioned in mysql documentation. so your data will be truncated — xkeshav, Oct 10 '11 at 06:53
Binary are stored as bytes. In phpmyadmin I can insert binay for example as: INSERT INTO test (password) VALUES (0x771C87E79B130E3FB966E424D7F1358D8DABBA0A26F288C0C1B5D8E3D95F2942100CA54B6824A7AC0964180A9426A1C37C371BA3FDBB000621FE175608C4B16C) — Oleg, Oct 10 '11 at 06:58

score 2 · Answer 1 · answered Oct 10 '11 at 08:03

2

That's a literal binary string that MySQL understands. I suspect that PHPUnit doesn't understand it. You could try using the equivalent for XML using entity references or maybe you'll luck out and the parser PHPUnit uses supports base64Binary.

answered Oct 10 '11 at 08:03

David Harkness

35,992
10
112
134

Could you please, explain what you suggest please? I'll take a look at the link. – Oleg Oct 10 '11 at 08:28
I'm sory, I don't understand how you suggest to solve the problem. – Oleg Oct 10 '11 at 13:26
If you're using DbUnit with PHPUnit, see [this answer](http://stackoverflow.com/questions/2107896/dbunit-and-binary-data/2108139#2108139) for an example. I am not familiar with DbUnit, but hopefully it will give you enough to go on. – David Harkness Oct 10 '11 at 20:13
Looking at [this blog post](http://digitalsandwich.com/archives/63-phpunit-database-extension-dbunit-port.html), it seems that the database extension in PHPUnit is a port of Java's DbUnit. Hopefully that means the base64 support is included. – David Harkness Oct 10 '11 at 20:16
There is a comment: This works with NDbUnit as well if you define it in your XML data file: MjBxdxxY7NbME2Ha6DKhepVpwio= in the answer link. It's not clear how in xml file, what tell that data are base64 and they should be converted to bytes by phpunit. – Oleg Oct 11 '11 at 06:50
I added base64 in value tag and get error that too much length, I think it happends because phpunit process it as strings (and base64 inceasing the length) not conerting to bytes: dxyH55sTDj+5ZuQk1/E1jY2rugom8ojAwbXY49lfKUIQDKVLaCSnrAlkGAqUJqHDfDcbo/27AAYh/hdWCMSxbA== base64 here 1318143535 0 2011-10-09 10:58:55 0 1 c9de97978010bd1335891cc8a37eaf96 – Oleg Oct 11 '11 at 06:52
I get an error data too long for column password, I don't know how to tell that data are base64. – Oleg Oct 11 '11 at 06:58
I tried to this one and got the same error: dxyH55sTDj+5ZuQk1/E1jY2rugom8ojAwbXY49lfKUIQDKVLaCSnrAlkGAqUJqHDfDcbo/27AAYh/hdWCMSxbA== – Oleg Oct 11 '11 at 07:00
1

I suspect that PHPUnit's database extension doesn't have this feature. The other option is as I wrote above: use XML entity references to embed binary values directly. It won't be pretty, but it might work because it depends solely on the XML parser handling standard XML entities. – David Harkness Oct 11 '11 at 11:22
I didn't quite understand the idea about embedding binary data and how it could help. Could you please explain what you meant? – Oleg Oct 11 '11 at 12:40

score 1 · Accepted Answer · answered Oct 13 '11 at 10:49

I see now a decision like this, using replacement decorator:

public function getDataSet($pFileName=null)
{

    if ($pFileName===null) {
        $vFileName = $this->_fixturesDir.'init_flat.xml';
    } else {
        $vFileName = $pFileName;
    }


    $ds = $this->createFlatXmlDataSet($vFileName);
    $rds = new PHPUnit_Extensions_Database_DataSet_ReplacementDataSet($ds);
    $rds->addSubStrReplacement('##HASH_wince4_1318143535##', hash('sha512', 'wince4' . '1318143535', true));
    $rds->addFullReplacement('##NULL##', null);        
    return $rds;        
}

In flatXML: It doesn't seem reliable, convinient and scalable. We try to replace some text with neccessary hash. I hope anybody could propose more appropriate decision of the problem.

You can always create your own version of the ReplacementDataSet decorator. I created a "HexadecimalAwareDataSet" decorator that does something like this but a bit more scalable. — Xunnamius, Jun 13 '17 at 08:19

testing database with binary type in mysql using phpunit

2 Answers2

Related