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):
CREATE OR REPLACE FUNCTION my_func (
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