SQL injection prevention

[Rahul Shar]

Hi All,

I am referring OWASP for SQL injection prevention and have two queries,

(1) Whats is "embedded parameters" related to parameterized stored procedures? - I couldn't find any difference between parameters and embedded parameters.

(2) Which one is better -
(a) Parametrized stored procedures with the principle of least privilege //Clubbing additional defense & high priority defense
(b) Parameterized stored procedures with the embedded parameters

- I think option (a) depends on the policy where you use stored procedures everywhere, so unless specified option (b) is more correct.

OWASP - https://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#Least_Privilege

TIA
Rahul

[Jeanne Boyarsky]

I don't see the word embedded parameters on that page. Do you have a link/quote that references them? (I'm not sure what the term refers to. It might be concatenating parameters with SQL which is bad. But I'm not sure that is what you mean.)

[Rahul Shar]

Hi Jeanne,

Yes, "embedded parameters" is not on OWASP page but was actually referred some other article related to SQL injection.

I think it is related to ORACLE Execute_Immediate or procedure where dynamic query or proc is generated.

Thanks
Rahul

[Ulf Dittmer]

The premise of your question seems to be to use stored procedures. While stored procs can add additional security, that is not directly related to SQL injection. Using prepared statements (in raw SQL queries) already takes care of that without the use of stored procs (which do have drawbacks as well as benefits).

[Rahul Shar]

I think embedded parameter is related to the example given in Owasp site -

https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet#Stored_Procedure_Examples

[Pablo Pastore]

I think I have found the answer for this, please "Find" the keyword "embedded parameter" from the pages below:


http://anubhavg.wordpress.com/2008/02/01/are-stored-procedures-safe-against-sql-injection/

http://palizine.plynt.com/issues/2006Jun/injection-stored-procedures/

[Pablo Pastore]

(b) should be true then.

[Jim Manico]

The solution to stop SQL Injection is to use PARAMETERIZED QUERIES. Also consider very good input validation, but Parameterized Queries is the most important technique. You need to do parameterization:
1) For SQL creation
2) When calling a stored procedure
3) Inside the stored procedure itself

Here are a few resources:

https://www.owasp.org/index.php/Query_Parameterization_Cheat_Sheet
https://www.youtube.com/watch?v=pypTYPaU7mM

Aloha,
Jim

[Christian Nicoll]

From my perspective, also after checking the (OWSAP) links, 2a is the better solution than 2b.

What do you think?