Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is it true that stored procedures prevent SQL injection attacks against PostgreSQL databases? I did a little research and found out that SQL Server, Oracle and MySQL are not safe against SQL injection even if we only use stored procedures. However, this problem does not exist in PostgreSQL.

Does the stored procedure implementation in PostgreSQL core prevent SQL injection attacks or is it something else? Or is PostgreSQL also susceptible to SQL injection even if we only use stored procedures? If so, please show me an example (e.g. book, site, paper, etc).

share|improve this question
just a note: postgres doesn't have stored procedure's it has stored functions, there are subtle differences between the two – xenoterracide Jan 4 '11 at 11:12
@xenot can you explain the diff? – Am1rr3zA Jan 4 '11 at 12:58
me? not really wikipedia can though it would also make a good question of it's own IMO. postgres's stored functions serve all the same purposes. – xenoterracide Jan 4 '11 at 14:14
The answers below are pretty good, so I'll add two things. 1 trust nothing that comes back from the user. Just because a field is hidden doesn't mean that the value can be trusted. 2. I've got a book coming out in February where I talk about SQL Injection (among other things). The book is for SQL Server, but the concepts should be similar. – mrdenny Jan 5 '11 at 1:31
Oddly, the top answers here are mostly OT dealing with SQL Server while the question is about Postgres. Here is a related answer for Postgres:…. There are a couple of others, try a search:… – Erwin Brandstetter Feb 1 '15 at 13:21
up vote 62 down vote accepted

No, stored procedures do not prevent SQL injection. Here's an actual example (from an in-house app someone created where I work) of a stored procedure that unfortunately permits SQL injection:

CREATE PROCEDURE [dbo].[sp_colunmName2]   
    @columnName as nvarchar(30),
    @type as nvarchar(30), 
    @searchText as nvarchar(30)           
    DECLARE @SQLStatement NVARCHAR(4000)
        SELECT @SQLStatement = 'select * from Stations where ' 
            + @columnName + ' ' + @type + ' ' + '''' + @searchText + '''' 

The developer's idea was to create a versatile search procedure, but the result is that the WHERE clause can contain anything the user wants, allowing a visit from little Bobby Tables.

Whether you use SQL statements or stored procedure doesn't matter. What matters is whether your SQL uses parameters or concatenated strings. Parameters prevent SQL injection; concatenated strings allow SQL injection.

share|improve this answer
Very good point! – jcolebrand Feb 9 '11 at 20:38
+1 : I've also seen some stored procedures that build the string so they can alter the fields being selected, or even the table being selected from. – Joe Feb 10 '11 at 2:09
Good example. +1 for Little Bobby Tables reference. – Joel Brown Sep 12 '11 at 19:27
+1 for very cool answer. – Soner Gönül Dec 18 '12 at 22:57
This is actually SQL Server syntax that doesn't make sense in Postgres. Also, strictly speaking, Postgres doesn't have "stored procedures", just "functions". Details:… – Erwin Brandstetter Feb 1 '15 at 13:13

SQL-Injection attacks are those where untrusted input is directly appended queries, allowing the user to effectively execute arbitrary code, as illustrated in this canonical XKCD comic.

Thus, we get the situation:

userInput = getFromHTML # "Robert ') Drop table students; --"

Query = "Select * from students where studentName = " + userInput

Stored Procedures are, in general, good defenses against SQL injection attacks because the incoming parameters are never parsed.

In a stored procedure, in most DBs (and programs, don't forget that precompiled queries count as stored procedures) look like the following:


create Stored procdure foo (
select * from students where studentName = :1

Then, when the program desires access, it calls foo(userInput) and happily retrieves the result.

A stored procedure is not a magical defense against SQL-Injection, as people are quite able to write bad stored procedures. However, pre-compiled queries, be they stored in the database or in the program, are much more difficult to open security holes in if you understand how SQL-Injection works.

You can read more about SQL-Injection:

share|improve this answer
+1 for code, and a link to how to attack your own code. – xenoterracide Jan 4 '11 at 11:13
+1 "if you understand how SQL-Injection works" :) – bsoist Jan 4 '11 at 15:37
"How to attack your own code" site. Apparently passwords are stored in plaintext there. – Matthew Schinckel Jan 6 '11 at 10:26

Yes, to some extent.
Stored Procedures alone will not prevent SQL Injection.

Let me first quote about SQL Injection from OWASP

A SQL injection attack consists of insertion or "injection" of a SQL query via the input data from the client to the application. A successful SQL injection exploit can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system. SQL injection attacks are a type of injection attack, in which SQL commands are injected into data-plane input in order to effect the execution of predefined SQL commands.

You have to sanitize user inputs and do not concatenate SQL statements, even if you are using stored procedure.

Jeff Attwood explained consequences of concatenating sql in "Give me parameterized SQL, or give me death"

Following is the interesting cartoon which comes to my mind whenever I hear SQL Injection alt text I think you got the point :-)

Have a look at SQL Injection Prevention Cheat Sheet, prevention methods are neatly explained...

share|improve this answer
Beat me to it :) – Brian Ballsun-Stanton Jan 4 '11 at 9:10
@Brian Ballsun-Stanton - :) – CoderHawk Jan 4 '11 at 9:13
Epic comic! +1 for it and Jeff's post about parametrized SQL – BrunoLM Jan 5 '11 at 13:34
Superb!! +1 for the cartoon.Never would i forget SQL Injection :) – balu Jan 14 '11 at 8:34

String concatenation is the cause of SQL Injection. This is avoided using parametrisation.

Stored procedures add an additional layer of security by enforcing invalid syntax when you concatenate, but are not "safer" if you use, say, dynamic SQL in them.

So, your code above is caused by concatenation of these strings

  • exec sp_GetUser '
  • x' AND 1=(SELECT COUNT(*) FROM Client); --
  • ' , '
  • monkey
  • '

This gives invalid syntax, luckily

Parametrising it would give

exec sp_GetUser 'x'' AND 1=(SELECT COUNT(*) FROM Client); --' , 'monkey'

This means

  • @UserName = x' AND 1=(SELECT COUNT(*) FROM Client); --
  • @Password = monkey

Now, in the code above you'll get no rows because I assume you have no user x' AND 1=(SELECT COUNT(*) FROM Client); --

If the stored proc looked like this (using concatenated dynamic SQL), then your parametrised stored proc call will still allow SQL Injection

SET @sql = 'SELECT userName from users where userName = ''' + 
               @UserName + 
               ''' and userPass = ''' +
               @Password +
EXEC (@sql)

So, as demonstrated, string concatenation is the main enemy for SQL injection

Stored procedures do add encapsulation, transaction handling, reduced permissions etc, but they can still be abused for SQL injection.

You can look on Stack Overflow for more about parametrisation

share|improve this answer

"SQL injection attacks happen when user input is improperly encoded. Typically, the user input is some data the user sends with her query, i.e. values in the $_GET, $_POST, $_COOKIE, $_REQUEST, or $_SERVER arrays. However, user input can also come from a variety of other sources, like sockets, remote websites, files, etc.. Therefore, you should really treat everything but constants (like 'foobar') as user input."

I've been investigating thoroughly on this subject recently and would like to share with others quite interesting material, thus, making this post more complete and instructive for everyone.

From YouTube

From Wikipedia


From PHP Manual

From Microsoft and Oracle

Stack Overflow

SQL injection scanner

share|improve this answer

Stored procedures don't magically prevent SQL injection, but they do make preventing it a heck of a lot easier. All you have to do is something like the following (Postgres example):

  IN in_user_id INT 
  SELECT user_id, name, address FROM my_table WHERE user_id = in_user_id; --BAM! SQL INJECTION IMMUNE!!

That's it! The problem only comes up when forming a query via string concatenation (i.e. dynamic SQL), and even in those cases you may be able to bind! (Depends on the database.)

How to avoid SQL injection in your dynamic query:

Step 1) Ask yourself if you really need a dynamic query. If you're sticking strings together just to set the input, then you're probably doing it wrong. (There are exceptions to this rule -- one exception is for reporting queries on some databases, you may have performance issues if you don't force it to compile a new query with each execution. But research this issue before you jump into that.)

Step 2) Research the proper way to set the variable for your particular RDBMS. For example Oracle lets you do the following (quoting from their docs):

sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' 
           || v_column || ' = :2';
EXECUTE IMMEDIATE sql_stmt USING amount, column_value; --INJECTION IMMUNE!!

Here you are still not concatenating the input. You are safely binding! Hooray!

If your database does not support something like the above (hopefully none of them are still this bad, but I wouldn't be surprised) - or if you still really must concatenate your input (like in the "sometimes" case of reporting queries as I hinted at above), then you must use a proper escaping function. Don't write it yourself. For example postgres provides the quote_literal() function. So you'd run:

sql_stmt := 'SELECT salary FROM employees WHERE name = ' || quote_literal(in_name);

This way if in_name is something devious like '[snip] or 1=1' (the "or 1=1" part means select all rows, allowing the user to see salaries he shouldn't!), then quote_literal saves your butt by making the resulting string:

SELECT salary FROM employees WHERE name = '[snip] or 1=1'

No results will be found (unless you have some employees with really weird names.)

That's the gist of it! Now let me just leave you with a link to a classic post by Oracle guru Tom Kyte on the subject of SQL Injection, to drive the point home: Linky

share|improve this answer

protected by Max Vernon Dec 1 '15 at 5:29

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.