Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

Photo
- - - - -

Mysql encryption

encryption mysql

  • Please log in to reply
3 replies to this topic

#1 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts
  • Location:Quebec City
  • Programming Language:Java, C++, C#, PHP, JavaScript, Visual Basic .NET, Transact-SQL, ActionScript

Posted 30 November 2011 - 06:52 AM

Hello,

I didn't know if I should put this question in the php section or in the database section, but since I beleive the answer will be in php, I guess I will post it here.

So as the title say, I want to encrypt the data in my database.
I use mysql, and to communicate with it, I use PHP + PDO.
And I always prepare my function, and use execute(array()) to call PDO like this
$sth = $dbh->prepare('select ... where id=:id');
$sth->execute(array(':id' => $id))

My first though was to override the execute and fetch function of PDO, to pass them throw a encryption function, but at the moment of execution, I do not know what columns has to be encrypted (such as varchar, text), and witch can't be encrypted (such as id, or date)

So my next tough was to rewrite some of my code, and insted of using execute(array), use bindParam or bindValue
It would be a pain to rewrite my code, but would work... except when I fetch, I would not know when the data is encrypted, and when it isn't

Anyone have an idea for this?
  • 0

#2 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 30 November 2011 - 01:53 PM

I have a BIG concern about this. Encryption can potentially change the size of the field. So if you have a Varchar(50), and you have a 50 character string, the encrypted string could be 75 characters.

I think a better question would be: what data NEEDS to be encrypted?
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 Vaielab

Vaielab

    Programming God

  • Expert Member
  • PipPipPipPipPipPipPip
  • 1382 posts
  • Location:Quebec City
  • Programming Language:Java, C++, C#, PHP, JavaScript, Visual Basic .NET, Transact-SQL, ActionScript

Posted 30 November 2011 - 03:25 PM

I read somewhere that you can predict the "grow size" (sorry can't find of a better way to say augmentation of the size) depending of the type of encoding you do.
So if I have a varchar(100) (example), and my encoding will do a maximum grow size of 20%, I can change my data to 120 without a problem
  • 0

#4 Alexander

Alexander

    YOL9

  • Moderator
  • 3963 posts
  • Location:Vancouver, Eh! Cleverness: 200
  • Programming Language:C, C++, PHP, Assembly

Posted 30 November 2011 - 10:00 PM

I read somewhere that you can predict the "grow size"

The term mainly used in such algorithms is 'expansion'. Each block/bit is expanded, and a larger text size is a byproduct of this.

It can be predicted (see below) due to padding.

I do not know what columns has to be encrypted (such as varchar, text), and witch can't be encrypted (such as id, or date)

You should consider rebuilding the queries to encrypt, where encrypted columns are required.

Juggling extended classes may take a long time, especially when deciding how to encrypt only certain things, and taking a lot of time on PHP's side for the encryption.

INSERT INTO `FOO` (id, address) VALUES (:id, AES_ENCRYPT(:address, :key))
...
SELECT AES_DECODE(name, :key) FROM `FOO` WHERE name = AES_ENCODE(:username, :key)

You cannot really rely on AES_DECRYPT failing on non-encrypted information however, as it may return garbage. You may have to encrypt all old data, and also find out what your key scheme will be.

---------------------

Expansion for AES 128 (16*8=128):
Expandedbytes = 16 * (strlen(string)/ 16) + 1)

string length is in bytes, not characters (as a multibyte character may increase the resulting size)

Alexander.
  • 0

All new problems require investigation, and so if errors are problems, try to learn as much as you can and report back.






Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download