Please Help Prevent SQL injection
Hi All,
Our old C# API dynamically generates SQL, and does the CRUD operations for several large software products.
A recent PEN test identified SQL injection is (easily) possible for one of the READ operations. It is called from hundreds of PHP files.
This part of the API was designed to allow developers to define the filters on a query. PHP developers have exposed this to authenticated users.
Can anyone suggest a way to validate the user input & ensure unsafe SQL injection can't occur?
- An example query received from the user might look like:
name = 'john' and title = 'MR' and (select top 1 group_id from groups where type = 1 and region = 'AUD') = 5
- I do have the list of valid columns that a user can access, so I can string match against it. Including valid dynamic columns as per the example above.
- I was thinking about splitting the string, then matching columns, however, I'm not confident with this. Perhaps there is a library that can convert SQL where clauses into a list of parameters ---- I can wish can't I?!?
Any help or suggestions would be greatly appreciated.
Our old C# API dynamically generates SQL, and does the CRUD operations for several large software products.
A recent PEN test identified SQL injection is (easily) possible for one of the READ operations. It is called from hundreds of PHP files.
This part of the API was designed to allow developers to define the filters on a query. PHP developers have exposed this to authenticated users.
Can anyone suggest a way to validate the user input & ensure unsafe SQL injection can't occur?
- An example query received from the user might look like:
name = 'john' and title = 'MR' and (select top 1 group_id from groups where type = 1 and region = 'AUD') = 5
- I do have the list of valid columns that a user can access, so I can string match against it. Including valid dynamic columns as per the example above.
- I was thinking about splitting the string, then matching columns, however, I'm not confident with this. Perhaps there is a library that can convert SQL where clauses into a list of parameters ---- I can wish can't I?!?
Any help or suggestions would be greatly appreciated.
