Inexpensive Datamasking for MySQL with ProxySQL
CC Attribution 2.0 Belgium:
You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor.
Descamps, Frédéric
Cannaò, René
During this session we will cover the last development in ProxySQL to supportregular expressions and how we can use this strong technique in correlationwith the query rules to anonymize live data quickly and transparently. We will explain the mechanism and how to generate these rules quickly. We showlive demo with all challenges we got from the Community and we finish thesession by an interactive brainstorm testing queries from the audience.
 Today I have the pleasure to present, to co-present with Rene. So you have seen Allie's work today and for the people that was here yesterday on Proxiesquiel. So he's the founder, the main developer and the one like if you saw Colin Slythe that spent all his night developing, it's him. So you can say thank you for Allie's work. Yeah, I am the one that wakes him up during the night to fix stuff, but so it's a shared work. First I want to ask you guys who knows what data masking is? Eh, not much. So from the guy who knows it, who needs data masking? Some of them, yeah. Okay, so first let's say this is the most fun part of the presentation, the most interesting one. So everything I say here is just, you know, yeah, it's for example, you can test it, don't try to go to prediction with what I said, oh but Fred says it's like that, yeah. This is for, you need to test if you want that, be sure of it, this is just, let's say, I want to show you something about Proxiesquiel. Everything that guy says, it's written in stone, so you have to follow him and this is, so this safe harbor is only for me, not for him. So let's start by who we are. So this is René, I will let him introduce himself. Hi René. Okay, so you can follow him on Twitter and he's the Proxiesquiel founder, right? And you also work at Dropbox. So this is me, you can follow me on Twitter for people who likes Twitter, sorry. And so I'm managing MySQL for a long time and I'm DevOps believer but nothing to do with that today, so no worries if you don't like DevOps. So first let's talk about what is Proxiesquiel. So this is the MySQL data stargate. That's it. This is all you need to know. If you need to know a bit more of details, but you have had a lot of... Yeah, so basically I mean it was already introduced in the previous session, so for who was not here, the idea is that Proxiesquiel has to be a middle layer between the application and the database server. So my vision is really, it's a gate that communicates those two layers and the wire staying in the middle between those two layers is able to do a lot of things like query routing, the processing of the query like blocking them or forwarding somewhere else, reviving them, caching. And of course the session, this session is about data masking. So again, this is one of the options that you can do having a middle layer in the middle. Thank you, Rune. So yes, this session is not about everything in Proxiesquiel, you saw the previous session, it's more about like the queries rewriting that you're going to do. So why do we like to use Proxiesquiel as a data masking solution? Because it's open source and free like in beer. So who knows other solution for MySQL to do data masking? Nobody? Svetta? Yeah, you can find some on Google, like GreenSQL for example. But this is quite costly, right? They are quite expensive. So it depends for what you need. But let's first talk about data masking. I didn't think about making a slide on that. But what do we want to, let's say to resolve with this data masking? What's the problem? The problem is that sometime you have developers and you have production data and developer needs a copy or just they need to access and read only the data, the production data. But maybe your data is sensitive. You have sensitive information there that you don't want that any developers in your, let's say enterprise have access. Maybe you have students that works there or whatever. And so you say, okay, maybe we have data that we need to mask. So the data should be there to performance to see, okay, need to retrieve, you need to sort or whatever, but it should be masked. You should not see it, right? So this is just about that. I will talk about that also later. But currently there is no, there is none very good solution on data masking right now. So this is one option that we did. So the other solutions are very expensive or they are just not working or not working properly and for the price sometimes it's not that good. This one is not worse than the other solutions, but because they are none are perfect. So if you have seen a bit of a Joro, if you want to discuss about Joro, he has a talk previously, is there a security expert in MySQL? Yeah, it's always possible to ask stuff or to deal with security. So watch out. So the best solution, if you really want to have a data masking, it will be to integrate this inside of the server. For example, just after the end layer API. So when the storage engine gets the data, that you modify the data there before exposing it to the client. But yeah, this is, I think this is a lot of work and currently it's not in our roadmap to do so, maybe in the future. So what is the concept of data masking using ProxySQL? So what we're going to do is that we're going to use regular expression. I'm very sorry about that because I will show you the regular expression after. We are very, let's say we are old in the IT world, so we are using Perl regular expression. So sorry for people who hate that. But you will see they are very easy. So what we want to do is we get the data and before we send the data to the client, we want to modify it. So we want to say, oh, we have some rules and for example, an easy one, we don't want that the developer or this user cannot see the credit card numbers. Makes sense that you don't want to share all your credit card numbers or something like that. Yes? Yeah, this is just. No, my example is just with credit card numbers. So I know you're not allowed. Like I said, you remember what I said is just for example. And the numbers you will see are not real. So don't try them. So what I mean, any data you want, emails or whatever, if you have some data that you don't want to share with the developer, you need to hide it. So in this case, when the developer will do a select, we need to find, oh, this column, we need to hide and we need to hide it and then return it. So this is what we're going to do in fact. So first, how we do that. So in ProxySQL, we need to create a user. So managing ProxySQL, maybe it was not obvious or it was too fast or you were not in the previous sessions about ProxySQL, but the admin interface is just SQL, almost standard SQL with some keywords just specific for ProxySQL, of course, but it's very easy. So if you want to create a user, you're going to do insert into MySQL user and give a user and a password. So in this case, we create a user name called devil. First thing we need to do, like I told you earlier, we want to hide this column, right? The non-credit card column. We want to hide it. And so what you're going to do is to check for this column, but people can do select star, right? So we want to avoid that too. So if the developer do select star, we need to avoid. Select star, you cannot do it on this table, right? So we need to create some rules to stop this and all variant of select star. So if the table is part of many tables, we need to do the same. So hide this name for every table. This is how it works, but this is also a constraint currently. So let's say you want to hide an email in user table, but you want to hide an email, same column name in another table. Here in this case, it won't work. They will both be hidden. So this is one of the limitations we have. So the second rule, we need to mask the field, right? So when the field is selected, we will need just to replace in my example, what I will do is I will show the column and I will just show the first two characters or numbers or whatever you want, just to and then put X's, right, to mask. And at least why I played the first two characters is in case you want to sort in your query or whatever, so you can do it. You want also to keep the column name because if you do OK, select email and then you do a lot of changes there. You want that in the result, you still have email. So because what we do, we rewrite a query. So we will send a query to MySQL and the query we sent, it's about changing already when we send it to MySQL. So we need, but we need to return the name. So an example, this number will become like this. So if we want to mask this CCNM from the table customer, we will need seven rules. So this is the rule number one. So as you can see, yeah, you see a very nice mouse pointer. So as you can see, yeah, the first one is quite easy to understand. We are looking for this CCNM, right? And if you have it, we will replace it. So this is not complicated, but we need to find because people, and I will discuss that after, they put some quotes, they put some parentheses, they will do everything. It's possible to get that information. So we need to try to focus on all that. Then it becomes a bit more complicated. So like I told you, I like Pearl. So this is also, we will try to find characters to find what we are looking for, and then we will make a concat, in fact. So what we're going to send to MySQL is just, okay, we don't tell it, give me the CCNM, just give me the two first characters of CCNM plus concatting with the x we want. So we don't send the full request to MySQL. So this is a rewrite of the query, in fact. Yes. Yeah. Okay. So basically, this is a completely rewrite of the query. One of the important things to notice if you're familiar with regular expression, of course, you have those that are nothing more than reference to the matching pattern. So this refers to this one, this one to the other one, and so on. So you can, and I think this is not possible with the MySQL rewrite plugin, doing back reference. And you cannot do that also with regular expression. You need the per regular expression because we have also case less and global here. So we want to do it every time it happens. So this is, so if you're familiar with ProxySQL, maybe you have never seen this. And this is because I asked Renee to do that and it will be released soon, right? So you can compile the code, but there is no packages with this information yet. So then we have extra rules again, but I won't detail them all. As you can see, it's fun. So also what we're going to do here is that if somebody do a select everything from the customer, we're just going to send to the user sending the query. That is not allowed to get that information. You can put everything you want there, of course. But so this is the message. Every time somebody will go, the developer, that user will do a select star. For example, in this case, we'll receive, okay, you're not allowed to do that. So we return an error. Again, same information because we need to take care of several different cases that I will show. So what are the limitations? The limitation is this. This is supported only with ProxySQL bigger equal to 1.4, not the previous version, right? So all fields of the same name, this is what I was saying before, will be masked even if you say, okay, this is for this table. We don't check about table in this case because maybe you can join several tables and it will be very complicated. If you find a very nice regular expression to do it, feel free to send it to me. I will be very, very happy. But currently, so all the fields with this name will be masked and however is the name of the table. So of course, regular expression are not, let's say, always safe and maybe the seven rules I give you here are not enough. Maybe somebody will come with a fancy solution. I hope so. So we can improve it. But this is already what we did until now. So yeah, if you want to create this, all these rules is quite painful or I think it's painful and need to remember them. So I made a just small bar script that you call it like that. So you give the column name you want and if there is a table that you want to avoid select star from it, you just do it like that. It creates the rules for you. So you can add as many rules. So how does it look like? So we select star from, I will show you after on live if we have time, but select star from customer, it was easy. What we're going to have here, we're going to have, OK, you are not allowed to do that, right? Select first name, last name, CCNM from customer, also easy. What we will have, we will have the first name, the last name and the CCNM value that will be what you call it, masked, right? Then more difficult and then here I need to thank Istoma here again. He's away. He was here. So I made some blog post and I got some contribution or by email or by comment to say, yeah, but if you do this query, what will happen? So this query is in two lines, right? My first rules, of course, it failed completely and you had the content. So now this is also by the query you have seen. We take care of that. So if the query is in multiple lines, it will work. If you make this CCNM twice in the same, this is really OK in my SQL, right? You can do that. The first rules we did without having this global, so when ProxySQL was only doing the regular expression and not the per regular expression, this one was hidden but not the other one. So now we take care of that. Now, yeah, there is a space here. Sorry, I forgot. Then we put some with the application there. No, what was it on? It doesn't see. Yeah, you don't see it because there are quotes in fact and they are removed by the syntax highlighter. But here you see it's a bit different. So you have quotes, the back quotes. So back quotes, single quotes, double quotes, all that is taking care. And yeah, this one also in two lines, it takes care of it. So this one is on two lines. This one is just because it's too long. It is when you have some function before the field, it works too. Again extra example that the rule, so all these with the rule I sent you, it will work. I mean, you won't have the data. It will be masked. So yeah, when you give a st1 for example, and here you add an analysis, this is taken in consideration. If you use CCNM and then you give an alias of it, you want to see threat and not CCNM or not CCNM, Concat CCNM or whatever. So it will work. Same when there is not the has. Yeah, here again, you have the quotes that you don't see, but there are some quotes on the slides. It takes care of it. Again here, this one has quotes. It takes care of it. And here with the quotes too, that's worked too. So all these case are taken in consideration by the seven rules you have seen. Extra. So yeah, this is taking consideration. When you put comments, everything, it will work. You won't see the data. This also, you will take care of it, so it won't work. This one will be blocked. This one will be blocked. So all these cases are handled by the ProxySQL with the rules. So it's quite already, let's say it goes far. Maybe not enough for everybody. I don't know. And this is what we would like to, we need you for feedback. So do you have some ideas of queries that won't work? So if I make a prepare statement, plus I generate some string, they put it into variable by some calculation, then make it into a clear statement. Next, it will check the content of the variable. So that will be prepared statement. Yeah, but your prepared statement will be already a... The prepared statement will be char of 3A plus char of something or whatever. Whatever functions I can mention. Yeah, I need to check. I need to test to see what we can do. If you prepare statement, it takes a string and then it will execute whatever was in the string. And I have found some fancy idea of what would be a string. If you find this fancy ID, send it to us, please, and we're going to test it. Well, I can add something else. I mean, this idea that I'm asking is also to not make exactly strict rules of the queries that need to be executed, because another approach is just to block any sort of query with exception of whatever is white listed. But of course, it's become more complicated. Yes. If you can send us an email, we will try it. We will try it. Yes. Yes, but there is not there. Yeah, we started the slides. We're saying making this in the database server in the just after the end layer API, for example, will be the best. But this is not the case right now. So currently, we don't have. Yeah, but you need to write that information. So you need to write that and see, so interfere with what the storage engine sends you and then verify, oh, this match what we don't want to see. And but this is quite also a lot of work to add it there for. And so, yeah. Yeah, it sounds easier for sure. Yeah, this sounds easier. It's not easier. This one is just you have it. You can do it. If you need that now today, you can do it like that. And first, maybe make some tests and parse all the queries your user are doing and just allow this one, block the other one and mask the one that you know. And but you have it. No, if you have to wait for my SQL to implement it, I cannot give you yet. It will be next release. So this is the difference. Here you have it. It doesn't cost you anything. But maybe it's not perfect. Yes, to be. So the question is, what is the column name and table name is the same? Yeah, didn't write that you are, it's more crazy than me. So I will test it and I will see. Yeah, I will test it or you can test it. This is this is Proxy SQL. Take it on GitHub, compile it and test it if you need it. Other questions? Yes. Yeah, write the responses. So yeah, the question here is why do we don't rewrite the response we get from my school instead of writing the query we send to my school and I will let Rene explain you. Actually, this was a request that had, I think more than one year ago and to be able to do this, the proxy need to understand the query and currently does not understand the query because there is no parsing. So it doesn't understand what the query represent and what is actually the query is asking. So it doesn't understand what is a column, what is a table and so on. Having, if the proxy will understand this, then what we'll do is that once the server replies, it will take the results that make the modifications and send to the client. It will be way greater overhead anyway. Because right now, whatever the server is responding is because it's already processed, the server is responding, the proxy just storing temporary and then sending row by row or in batches depending from the protocol to the client. So there is no extra processing. Doing extra processing will be way more costly. Yes. But you can have alias. So you can run a select ccnum as whatever blah. And so the proxy won't know. Here we process it once when we send the data, right? So we do it once. Let's say you do a select and you have thousand records or, you know, a thousand hundred records and stuff like that. You will need for each rows modified and this is too much overhead for proxy SQL. In this way, the data never leaves the database server. I don't know if that makes any difference when it comes to security constraint, but the data will never leave the database server. Let's see it again. Oh, well, you have. So the question was what if the same proxy is used also for protection traffic and not just for development. So the first thing, can I go back? So here you see you have the username. So all this transformation apply only to that user. That might be the user used by the developer. Yes, this. Yes, I mean, the question was about how to try to upload the main proxy in case there is too much processing here. And of course you can always create multiple layer of proxy. So one proxy is sending traffic to the other and you can, for example, decide to do the processing in just one of the proxy while the other one is just forward in traffic and not be affected by the times required for the processing. By the way, the proxy also collect metric on how much time is spent processing those rules. So it is easy to identify if processing those rules is a time consuming operation or not. So thank you very much and let's call the next speaker. Thank you, Renée.
 Today I have the pleasure to present, to co-present with Rene.  So you have seen Allie's work today and for the people that was here yesterday on Proxiesquiel.  So he's the founder, the main developer and the one like if you saw Colin Slythe that  spent all his night developing, it's him.  So you can say thank you for Allie's work.  Yeah, I am the one that wakes him up during the night to fix stuff, but so it's a shared  work.  First I want to ask you guys who knows what data masking is?  Eh, not much.  So from the guy who knows it, who needs data masking?  Some of them, yeah.  Okay, so first let's say this is the most fun part of the presentation, the most interesting  one.  So everything I say here is just, you know, yeah, it's for example, you can test it, don't  try to go to prediction with what I said, oh but Fred says it's like that, yeah.  This is for, you need to test if you want that, be sure of it, this is just, let's say, I  want to show you something about Proxiesquiel.  Everything that guy says, it's written in stone, so you have to follow him and this is,  so this safe harbor is only for me, not for him.  So let's start by who we are.  So this is René, I will let him introduce himself.  Hi René.  Okay, so you can follow him on Twitter and he's the Proxiesquiel founder, right?  And you also work at Dropbox.  So this is me, you can follow me on Twitter for people who likes Twitter, sorry.  And so I'm managing MySQL for a long time and I'm DevOps believer but nothing to do with  that today, so no worries if you don't like DevOps.  So first let's talk about what is Proxiesquiel.  So this is the MySQL data stargate.  That's it.  This is all you need to know.  If you need to know a bit more of details, but you have had a lot of...  Yeah, so basically I mean it was already introduced in the previous session, so for who was not  here, the idea is that Proxiesquiel has to be a middle layer between the application  and the database server.  So my vision is really, it's a gate that communicates those two layers and the wire staying in the  middle between those two layers is able to do a lot of things like query routing, the  processing of the query like blocking them or forwarding somewhere else, reviving them,  caching.  And of course the session, this session is about data masking.  So again, this is one of the options that you can do having a middle layer in the middle.  Thank you, Rune.  So yes, this session is not about everything in Proxiesquiel, you saw the previous session,  it's more about like the queries rewriting that you're going to do.  So why do we like to use Proxiesquiel as a data masking solution?  Because it's open source and free like in beer.  So who knows other solution for MySQL to do data masking?  Nobody?  Svetta?  Yeah, you can find some on Google, like GreenSQL for example.  But this is quite costly, right?  They are quite expensive.  So it depends for what you need.  But let's first talk about data masking.  I didn't think about making a slide on that.  But what do we want to, let's say to resolve with this data masking?  What's the problem?  The problem is that sometime you have developers and you have production data and developer  needs a copy or just they need to access and read only the data, the production data.  But maybe your data is sensitive.  You have sensitive information there that you don't want that any developers in your,  let's say enterprise have access.  Maybe you have students that works there or whatever.  And so you say, okay, maybe we have data that we need to mask.  So the data should be there to performance to see, okay, need to retrieve, you need to  sort or whatever, but it should be masked.  You should not see it, right?  So this is just about that.  I will talk about that also later.  But currently there is no, there is none very good solution on data masking right now.  So this is one option that we did.  So the other solutions are very expensive or they are just not working or not working  properly and for the price sometimes it's not that good.  This one is not worse than the other solutions, but because they are none are perfect.  So if you have seen a bit of a Joro, if you want to discuss about Joro, he has a talk  previously, is there a security expert in MySQL?  Yeah, it's always possible to ask stuff or to deal with security.  So watch out.  So the best solution, if you really want to have a data masking, it will be to integrate  this inside of the server.  For example, just after the end layer API.  So when the storage engine gets the data, that you modify the data there before exposing  it to the client.  But yeah, this is, I think this is a lot of work and currently it's not in our roadmap  to do so, maybe in the future.  So what is the concept of data masking using ProxySQL?  So what we're going to do is that we're going to use regular expression.  I'm very sorry about that because I will show you the regular expression after.  We are very, let's say we are old in the IT world, so we are using Perl regular expression.  So sorry for people who hate that.  But you will see they are very easy.  So what we want to do is we get the data and before we send the data to the client, we  want to modify it.  So we want to say, oh, we have some rules and for example, an easy one, we don't want  that the developer or this user cannot see the credit card numbers.  Makes sense that you don't want to share all your credit card numbers or something like  that.  Yes?  Yeah, this is just.  No, my example is just with credit card numbers.  So I know you're not allowed.  Like I said, you remember what I said is just for example.  And the numbers you will see are not real.  So don't try them.  So what I mean, any data you want, emails or whatever, if you have some data that you  don't want to share with the developer, you need to hide it.  So in this case, when the developer will do a select, we need to find, oh, this column,  we need to hide and we need to hide it and then return it.  So this is what we're going to do in fact.  So first, how we do that.  So in ProxySQL, we need to create a user.  So managing ProxySQL, maybe it was not obvious or it was too fast or you were not in the  previous sessions about ProxySQL, but the admin interface is just SQL, almost standard  SQL with some keywords just specific for ProxySQL, of course, but it's very easy.  So if you want to create a user, you're going to do insert into MySQL user and give a user  and a password.  So in this case, we create a user name called devil.  First thing we need to do, like I told you earlier, we want to hide this column, right?  The non-credit card column.  We want to hide it.  And so what you're going to do is to check for this column, but people can do select  star, right?  So we want to avoid that too.  So if the developer do select star, we need to avoid.  Select star, you cannot do it on this table, right?  So we need to create some rules to stop this and all variant of select star.  So if the table is part of many tables, we need to do the same.  So hide this name for every table.  This is how it works, but this is also a constraint currently.  So let's say you want to hide an email in user table, but you want to hide an email,  same column name in another table.  Here in this case, it won't work.  They will both be hidden.  So this is one of the limitations we have.  So the second rule, we need to mask the field, right?  So when the field is selected, we will need just to replace in my example, what I will  do is I will show the column and I will just show the first two characters or numbers or  whatever you want, just to and then put X's, right, to mask.  And at least why I played the first two characters is in case you want to sort in your query  or whatever, so you can do it.  You want also to keep the column name because if you do OK, select email and then you do  a lot of changes there.  You want that in the result, you still have email.  So because what we do, we rewrite a query.  So we will send a query to MySQL and the query we sent, it's about changing already  when we send it to MySQL.  So we need, but we need to return the name.  So an example, this number will become like this.  So if we want to mask this CCNM from the table customer, we will need seven rules.  So this is the rule number one.  So as you can see, yeah, you see a very nice mouse pointer.  So as you can see, yeah, the first one is quite easy to understand.  We are looking for this CCNM, right?  And if you have it, we will replace it.  So this is not complicated, but we need to find because people, and I will discuss that  after, they put some quotes, they put some parentheses, they will do everything.  It's possible to get that information.  So we need to try to focus on all that.  Then it becomes a bit more complicated.  So like I told you, I like Pearl.  So this is also, we will try to find characters to find what we are looking for, and then  we will make a concat, in fact.  So what we're going to send to MySQL is just, okay, we don't tell it, give me the CCNM,  just give me the two first characters of CCNM plus concatting with the x we want.  So we don't send the full request to MySQL.  So this is a rewrite of the query, in fact.  Yes.  Yeah.  Okay.  So basically, this is a completely rewrite of the query.  One of the important things to notice if you're familiar with regular expression, of course,  you have those that are nothing more than reference to the matching pattern.  So this refers to this one, this one to the other one, and so on.  So you can, and I think this is not possible with the MySQL rewrite plugin, doing back  reference.  And you cannot do that also with regular expression.  You need the per regular expression because we have also case less and global here.  So we want to do it every time it happens.  So this is, so if you're familiar with ProxySQL, maybe you have never seen this.  And this is because I asked Renee to do that and it will be released soon, right?  So you can compile the code, but there is no packages with this information yet.  So then we have extra rules again, but I won't detail them all.  As you can see, it's fun.  So also what we're going to do here is that if somebody do a select everything from the  customer, we're just going to send to the user sending the query.  That is not allowed to get that information.  You can put everything you want there, of course.  But so this is the message.  Every time somebody will go, the developer, that user will do a select star.  For example, in this case, we'll receive, okay, you're not allowed to do that.  So we return an error.  Again, same information because we need to take care of several different cases that  I will show.  So what are the limitations?  The limitation is this.  This is supported only with ProxySQL bigger equal to 1.4, not the previous version, right?  So all fields of the same name, this is what I was saying before, will be masked even if  you say, okay, this is for this table.  We don't check about table in this case because maybe you can join several tables and it will  be very complicated.  If you find a very nice regular expression to do it, feel free to send it to me.  I will be very, very happy.  But currently, so all the fields with this name will be masked and however is the name  of the table.  So of course, regular expression are not, let's say, always safe and maybe the seven  rules I give you here are not enough.  Maybe somebody will come with a fancy solution.  I hope so.  So we can improve it.  But this is already what we did until now.  So yeah, if you want to create this, all these rules is quite painful or I think it's painful  and need to remember them.  So I made a just small bar script that you call it like that.  So you give the column name you want and if there is a table that you want to avoid select  star from it, you just do it like that.  It creates the rules for you.  So you can add as many rules.  So how does it look like?  So we select star from, I will show you after on live if we have time, but select star from  customer, it was easy.  What we're going to have here, we're going to have, OK, you are not allowed to do that,  right?  Select first name, last name, CCNM from customer, also easy.  What we will have, we will have the first name, the last name and the CCNM value that  will be what you call it, masked, right?  Then more difficult and then here I need to thank Istoma here again.  He's away.  He was here.  So I made some blog post and I got some contribution or by email or by comment to say, yeah, but  if you do this query, what will happen?  So this query is in two lines, right?  My first rules, of course, it failed completely and you had the content.  So now this is also by the query you have seen.  We take care of that.  So if the query is in multiple lines, it will work.  If you make this CCNM twice in the same, this is really OK in my SQL, right?  You can do that.  The first rules we did without having this global, so when ProxySQL was only doing the  regular expression and not the per regular expression, this one was hidden but not the  other one.  So now we take care of that.  Now, yeah, there is a space here.  Sorry, I forgot.  Then we put some with the application there.  No, what was it on?  It doesn't see.  Yeah, you don't see it because there are quotes in fact and they are removed by the syntax  highlighter.  But here you see it's a bit different.  So you have quotes, the back quotes.  So back quotes, single quotes, double quotes, all that is taking care.  And yeah, this one also in two lines, it takes care of it.  So this one is on two lines.  This one is just because it's too long.  It is when you have some function before the field, it works too.  Again extra example that the rule, so all these with the rule I sent you, it will work.  I mean, you won't have the data.  It will be masked.  So yeah, when you give a st1 for example, and here you add an analysis, this is taken  in consideration.  If you use CCNM and then you give an alias of it, you want to see threat and not CCNM  or not CCNM, Concat CCNM or whatever.  So it will work.  Same when there is not the has.  Yeah, here again, you have the quotes that you don't see, but there are some quotes on  the slides.  It takes care of it.  Again here, this one has quotes.  It takes care of it.  And here with the quotes too, that's worked too.  So all these case are taken in consideration by the seven rules you have seen.  Extra.  So yeah, this is taking consideration.  When you put comments, everything, it will work.  You won't see the data.  This also, you will take care of it, so it won't work.  This one will be blocked.  This one will be blocked.  So all these cases are handled by the ProxySQL with the rules.  So it's quite already, let's say it goes far.  Maybe not enough for everybody.  I don't know.  And this is what we would like to, we need you for feedback.  So do you have some ideas of queries that won't work?  So if I make a prepare statement, plus I generate some string, they put it into variable  by some calculation, then make it into a clear statement.  Next, it will check the content of the variable.  So that will be prepared statement.  Yeah, but your prepared statement will be already a...  The prepared statement will be char of 3A plus char of something or whatever.  Whatever functions I can mention.  Yeah, I need to check.  I need to test to see what we can do.  If you prepare statement, it takes a string and then it will execute whatever was in the string.  And I have found some fancy idea of what would be a string.  If you find this fancy ID, send it to us, please, and we're going to test it.  Well, I can add something else.  I mean, this idea that I'm asking is also to not make exactly strict rules of the queries  that need to be executed, because another approach is just to block any sort of query  with exception of whatever is white listed.  But of course, it's become more complicated.  Yes.  If you can send us an email, we will try it.  We will try it.  Yes.  Yes, but there is not there.  Yeah, we started the slides.  We're saying making this in the database server in the just after the end layer API, for example,  will be the best.  But this is not the case right now.  So currently, we don't have.  Yeah, but you need to write that information.  So you need to write that and see, so interfere with what the storage engine sends you and  then verify, oh, this match what we don't want to see.  And but this is quite also a lot of work to add it there for.  And so, yeah.  Yeah, it sounds easier for sure.  Yeah, this sounds easier.  It's not easier.  This one is just you have it.  You can do it.  If you need that now today, you can do it like that.  And first, maybe make some tests and parse all the queries your user are doing and just  allow this one, block the other one and mask the one that you know.  And but you have it.  No, if you have to wait for my SQL to implement it, I cannot give you yet.  It will be next release.  So this is the difference.  Here you have it.  It doesn't cost you anything.  But maybe it's not perfect.  Yes, to be.  So the question is, what is the column name and table name is the same?  Yeah, didn't write that you are, it's more crazy than me.  So I will test it and I will see.  Yeah, I will test it or you can test it.  This is this is Proxy SQL.  Take it on GitHub, compile it and test it if you need it.  Other questions?  Yes.  Yeah, write the responses.  So yeah, the question here is why do we don't rewrite the response we get from  my school instead of writing the query we send to my school and I will let  Rene explain you.  Actually, this was a request that had, I think more than one year ago and to be  able to do this, the proxy need to understand the query and currently does  not understand the query because there is no parsing.  So it doesn't understand what the query represent and what is actually the  query is asking.  So it doesn't understand what is a column, what is a table and so on.  Having, if the proxy will understand this, then what we'll do is that once the  server replies, it will take the results that make the modifications and send  to the client.  It will be way greater overhead anyway.  Because right now, whatever the server is responding is because it's already  processed, the server is responding, the proxy just storing temporary and then  sending row by row or in batches depending from the protocol to the client.  So there is no extra processing.  Doing extra processing will be way more costly.  Yes.  But you can have alias.  So you can run a select ccnum as whatever blah.  And so the proxy won't know.  Here we process it once when we send the data, right?  So we do it once.  Let's say you do a select and you have thousand records or, you know, a thousand  hundred records and stuff like that.  You will need for each rows modified and this is too much overhead for proxy  SQL.  In this way, the data never leaves the database server.  I don't know if that makes any difference when it comes to security constraint, but  the data will never leave the database server.  Let's see it again.  Oh, well, you have.  So the question was what if the same proxy is used also for protection  traffic and not just for development.  So the first thing, can I go back?  So here you see you have the username.  So all this transformation apply only to that user.  That might be the user used by the developer.  Yes, this.  Yes, I mean, the question was about how to try to upload the main proxy in case there  is too much processing here.  And of course you can always create multiple layer of proxy.  So one proxy is sending traffic to the other and you can, for example, decide to do the  processing in just one of the proxy while the other one is just forward in traffic  and not be affected by the times required for the processing.  By the way, the proxy also collect metric on how much time is spent processing those  rules.  So it is easy to identify if processing those rules is a time consuming operation or not.  So thank you very much and let's call the next speaker.  Thank you, Renée.
Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_0_47.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_48_131.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_132_1055.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_1056_2123.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_2124_2927.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_2928_6047.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_6048_7451.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_7452_7559.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_7560_8231.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_8232_9479.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_9480_10103.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_10104_14939.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_14940_15251.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_15252_16499.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_16500_16943.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_16944_19307.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_19308_21215.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_21216_21803.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_21804_23831.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_23832_26483.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_26484_26795.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_26796_27419.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_27420_28043.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_28044_28355.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_28356_28643.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_28644_28979.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_28980_30071.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_30072_32099.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_32100_35999.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_36000_37103.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_37104_38339.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_38340_38963.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_38964_40835.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_40836_42731.png Inexpensive_Datamasking_for_MySQL_with_ProxySQL_data_anonymization_for_developers_42732_42848.png