[sqlite] SQLite file Validation
Hi all, Total noob question for you guys: Background: I have a project where there are many components touching different SQLite dbs. Devs were told to make sure they are validating their sql statements, but as I have seen in the code, few of them have. On some components they have used the BIND statement, which will help. We are dealing with media file inputs mostly on this project, so people might have files named Michael Jackson; Beat it; Thriller or something like that. My concern is possible sql injections throughout the different code. We are on a tight deadline and we are unable to get back and recode a bunch of components. Question: Do you think that instead of getting them go back throughout their code, it is feasible to create a function that just eliminates the ; and replaces it with a ,? And if so, any suggested code? Thanks so much for your time. -Steve -- View this message in context: http://old.nabble.com/SQLite-file-Validation-tp28612927p28612927.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite file Validation
On May 19, 2010, at 12:24, seandakid wrote: Question: Do you think that instead of getting them go back throughout their code, it is feasible to create a function that just eliminates the ; and replaces it with a ,? And if so, any suggested code? On one hand, you have something that is safer and more efficient (probably tons faster depending on your app since you'd be able to reuse statements). On the other, you have something that will increase your technical debt and give you more places to hide bugs (with false hope that you can figure out the difference between code and data magically in a new layer). -- Dustin Sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite file Validation
Thanks for the quick reply Dustin. That was my concern as well.. it might create more issues than it will solve. One of the devs suggested this code example: int makeSQLtight(const TCHAR* update); S Dustin Sallings wrote: On May 19, 2010, at 12:24, seandakid wrote: Question: Do you think that instead of getting them go back throughout their code, it is feasible to create a function that just eliminates the ; and replaces it with a ,? And if so, any suggested code? On one hand, you have something that is safer and more efficient (probably tons faster depending on your app since you'd be able to reuse statements). On the other, you have something that will increase your technical debt and give you more places to hide bugs (with false hope that you can figure out the difference between code and data magically in a new layer). -- Dustin Sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://old.nabble.com/SQLite-file-Validation-tp28612927p28613149.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite file Validation
When you say create a function -- sure you can write your own program that would do that -- but it sounds like you want something to intercept all the calls, yes? You could create an insert or update trigger. Then write your own cleanup sqlite function to stick in the trigger. Shouldn't be hard to do at all. http://www.sqlite.org/capi3ref.html#sqlite3_create_function Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of seandakid Sent: Wed 5/19/2010 2:24 PM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite file Validation Hi all, Total noob question for you guys: Background: I have a project where there are many components touching different SQLite dbs. Devs were told to make sure they are validating their sql statements, but as I have seen in the code, few of them have. On some components they have used the BIND statement, which will help. We are dealing with media file inputs mostly on this project, so people might have files named Michael Jackson; Beat it; Thriller or something like that. My concern is possible sql injections throughout the different code. We are on a tight deadline and we are unable to get back and recode a bunch of components. Question: Do you think that instead of getting them go back throughout their code, it is feasible to create a function that just eliminates the ; and replaces it with a ,? And if so, any suggested code? Thanks so much for your time. -Steve -- View this message in context: http://old.nabble.com/SQLite-file-Validation-tp28612927p28612927.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite file Validation
I do need to add that the docs say the create_function must be done in the same thread as the defined funtion is running. So if you're mutli-threaded it won't work unless each thread defines it (still doable though). Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of Black, Michael (IS) Sent: Wed 5/19/2010 2:49 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite file Validation When you say create a function -- sure you can write your own program that would do that -- but it sounds like you want something to intercept all the calls, yes? You could create an insert or update trigger. Then write your own cleanup sqlite function to stick in the trigger. Shouldn't be hard to do at all. http://www.sqlite.org/capi3ref.html#sqlite3_create_function Michael D. Black Senior Scientist Northrop Grumman Mission Systems From: sqlite-users-boun...@sqlite.org on behalf of seandakid Sent: Wed 5/19/2010 2:24 PM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite file Validation Hi all, Total noob question for you guys: Background: I have a project where there are many components touching different SQLite dbs. Devs were told to make sure they are validating their sql statements, but as I have seen in the code, few of them have. On some components they have used the BIND statement, which will help. We are dealing with media file inputs mostly on this project, so people might have files named Michael Jackson; Beat it; Thriller or something like that. My concern is possible sql injections throughout the different code. We are on a tight deadline and we are unable to get back and recode a bunch of components. Question: Do you think that instead of getting them go back throughout their code, it is feasible to create a function that just eliminates the ; and replaces it with a ,? And if so, any suggested code? Thanks so much for your time. -Steve -- View this message in context: http://old.nabble.com/SQLite-file-Validation-tp28612927p28612927.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite file Validation
On May 19, 2010, at 12:46, seandakid wrote: Thanks for the quick reply Dustin. That was my concern as well.. it might create more issues than it will solve. One of the devs suggested this code example: int makeSQLtight(const TCHAR* update); I'm all for developer laziness, but holistically. Doing lots of work to asymptotically approach safe with the effect of encouraging unsafe practices. This conversation came up a few times on reddit a month or so ago. People brought up things like mysql_real_escape (that is, something (unfortunately) widely used and allegedly well-tested). Rather than stopping at assuming it was wrong, I just did a google search for exploits within it. There were lots. It's not worth it. If you do things right, bad results become impossible. If you do things wrong, you'll never get to a solution, regardless of how quick it appears. :) -- Dustin Sallings ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite file Validation
Devs were told to make sure they are validating their sql statements, but as I have seen in the code, few of them have Question: Do you think that instead of getting them go back throughout their code, it is feasible to create a function that just eliminates the ; and replaces it with a ,? And if so, any suggested code? I have this programmer who keeps writing stupid code despite explicit guidance. I want an algorithm that converts his bad inputs into good inputs. Make sure to let us know if you come up with a general solution. Eric -- Eric A. Smith You are standing in an open field west of a white house, with a boarded front door. There is a small mailbox here. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
