B
bighead4694
Hello,
Can any body tell me how to avoid SQL insertion attacks in Java?
Thanks a lot
bighead
Can any body tell me how to avoid SQL insertion attacks in Java?
Thanks a lot
bighead
L
Lee Fesperman
Use prepared statements -- java.sql.PreparedStatement ... if your JDBC driver and
backend support them natively. I know that MySQL didn't provide native support until
recently. What is your backend DBMS?
D
Dotty
Firewall and login/password.
BTW, I have an application that inserts 4096 rows
using one INSERT statement. (MySQL)
M
Malte
Apart from doublechecking all input variables, disallow dynamic SQL.
Most banks, AFIK, allow only static SQL, although this is also for
performance reasons. Use CallableStatement and stored procedures, ie get
as much SQL out of the Java layer as possible.
D
Dotty
Malte said:
You really need to block any unauthorized source from sending text
strings to any of your machines, not just the SQL input stream.
B
bighead4694
My backend DBMS is Oracle.
M
monroeds
Do you have a DBA?
C
Chris Smith
Dotty said:
You don't seem to understand what an SQL insertion attack (aka SQL
injection attack) really is. I suggest Google.
--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation
L
Lee Fesperman
Great! AFAIK, they have native support for prepared statements. Simply pass all
arguments received externally as ? parameters to your prepared statement. This will
protect you against SQL insertion/injection attacks.
Please respond if you need more help/info...
S
steve
too many Knowledge less idiots in this group, disregard most of the replies
you have been given, they are complete crap.
1.do something like the following:
where "dffdfde.ffdfsreewrw"= 'package.subroutine'
which in reality is:
String The_qry = "{ ? = call
external_user.PACKAGE_02.return_relations(?,?)}";
now the actual code!!
String The_qry = "{ ? = call external_user.dffdfde.ffdfsreewrw(?,?)}";
cstmt = (OracleCallableStatement) dbconn.prepareCall(The_qry);
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.setString(2, "DUMMY");
cstmt.setint(3,4); // the type of query
then in the oracle database have a package that returns the data, DO NOT
allow any sort of java side sql, other than thru packages.
( even if the hacker ,fools with the above string , they can gain nothing,
because "external user" has no real privs.)
2. also in the package, check the "id" of the current user, ( that is the
"magic" number given to the session, check that against the user that is
submitting the call-ins, and disallow if it is not the same.
3. give the packages some totally meaningless name.
4. pass in "key" flags, not values.
5. give "External_user" NO DIRECT PRIVS, set all routines to security
"auth_definer"
S
steve
no it will not!!.
L
Lee Fesperman
steve said:
Kindly explain, or apologize for calling people idiots.
S
steve
Nope , i don't think so, get over it.
Then take a look at the replies.
I find it offensive when i see piss poor replies to people who require
genuine help.
We have a guy that has requested help, which is fine.
However i see only 1 reasonable reply, and that is to use stored procedures,
and call outs. (remembering that the
'helpee' did not give any information on the application/security level, but
DID state he was using Oracle)
and then there is your reply. ( use prepared statements && ?)
consider the code:
String sql =
"Select object_code,client_file_name,filedatestamp from
client_code_java where deleted=0 and rep_index=?";
PreparedStatement st = dbconn.prepareStatement(sql);
st.setString(1, indexkey); // Bind the replication index
rset = st.executeQuery(); // Execute Query
this satisfies your reply of "using ?" and prepared statements
Is it secure?
hmm
1. String sql ="Select * from client_code_java where ?=null";
2. String sql ="Select * from client_code_java where ?<>null";
( null can be anything and 'nothing', it is null)
3. indexkey='"";
so far that is 3 ways to attack this "perfect" system.
that's not even considering how to easily recover passwords from an oracle
thin JDBC connection.
Which would make any prepared statement and '?' F**&K useless.
now consider how you would hack:
String The_qry ="{ call
external_user.fgfdgfddfg.asa(?,?,?,?,?,?,?,?,?,?,?,?,?)}";
where "external_user" has connect privs. only.
replies on a postage stamp please.
if people require help, then help, but if you have not thought about the
question then STFU.
Steve.
T
Thomas Kellerer
I don't understand your concerns. In your code the actual sql is hardcoded in
the Java code. The only thing that can be injected into that Sql is the value
used for rep_index. I can't see a way to inject real SQL "code" in there,
because the PreparedStatement will not accept it.
None of those examples will work or return anything from the database. I
actually doubt if they would be exectued at all. AFAIK the ? paramters are only
allowed for values not for columns (which you are doing).
My understanding is, that "SQL injection" means that the user can enter values
in a front end that will eventually end up being used as parameters in a SQL
statement.
Are you implying that your SQL strings would be entered by the user? Taking your
initial example into account, I would only think the user would enter the value
of the indexkey parameter.
Could you explain this attack in more detail? Should the indexkey contain a
single quote or a double quote? In either case this would either stop the SQL
from working (if rep_index was a numeric field) - depending on the DBMS used or
simply return rows where the rep_index is equal to a single quote (or double
quote) depending on what your example was.
That is another topic and is - in my opinion - not related to SQL injection. But
this depends largely on the architecture beeing used e.g. a web application
where the appliation server is doing the connection and not the user itself
could well use a thin JDBC connection (assuming application server and database
server are well secured from the outside)
Thomas
A
Antti S. Brax
Well, then you should be pretty "f**&k" ashamed about giving a
piss poor reply yourself.
Does not work. Also, even if this example was valid, if code like
this is used then no insertion attack is needed. All the attacker
needs to do is to know the right column name. Insertion attack
means that you insert something that alters the logic in the SQL
statement.
For example if the SQL statement is created by catenating strings
and the statement is:
"DELETE FROM table WHERE col = '" + value + "'"
an insertion attack would be inserting the value
"0' OR col <> '0"
thus producing a statement:
DELETE FROM table WHERE col = '0' OR col said:
Does not work.
Using PreparedStatement solves this. Characters that have a
special meaning in SQL are encoded before they are inserted
into a prepared statement. So this is not a working attack.
Incorrect. None of your examples work.
Practise what you preach.
C
Chris Smith
steve said:
Stored procedures have absolutely nothing to do with SQL injection
attacks, except for the incidental detail that in JDBC,
CallableStatement is a subtype of PreparedStatement and implements the
same parameterized query mechanism. If you try to call a stored
procedure without CallableStatement or PreparedStatement -- something
that's quite possible in most databases -- then you are just as
vulnerable to SQL injection as someone executing SQL select statements.
On the other hand, you could certainly stand to learn something about
JDBC and prepared statements. One of the fundamental concepts of
prepared statements is that you are responsible only for providing the
data, and the database driver guarantees that it will be passed properly
to the DBMS by including it in SQL. How that happens is implementation-
specific, but one of several possible techniques is to do quoting of
special characters, making SQL injection attacks null and void. Another
possibility is to actually send the data in separate binary streams
according to a vendor-specific wire protocol for statement parameters,
which is practically speaking even more effective at solving SQL
injection, since it leaves less room for driver bugs to open security
holes.
Running SQL code as unprivileged accounts is also an effective layered
security technique to provide extra protection against injection
attacks. However, it makes for a very poor primary solution; and your
muddying the waters with all that nonsense about stored procedures
creates just enough confusion for someone to open up a security hole by
accident. Bad job.
I'll leave you to read other replies on why your attacks against a
system using prepared statements will fail. Lee and Antii are exactly
right on this matter. One nit, though:
Technically, the two prepared statements above are actually valid.
(However, in ANSI SQL they will always return zero rows because
comparisons with NULL yield NULL rather than true or false.) However,
you can't plug in a column name there; only a literal value. This is,
of course, incredibly useless, so your queries are quite valid but still
unlikely to be correct.
You're right; you can't. That's because you're essentially using a
prepared statement there. The unprivileged user helps as well.
However, the lame attempt at obfuscation for security purposes convinces
me of two things:
1. You're never coming near a database schema I have to work on if I
have any say whatsoever on the matter.
2. You are obviously not qualified to be making security decisions in
any kind of critical environment.
Nevertheless, notice that if you write (fixing your naming convention
issues):
String theQuery = "{call external_user.fgfsgfddfg.asa("
+ firstField + "," + secondField ... + ")}";
Then you would be using the same stored procedure but be quite
vulnerable to an SQL injection attack, leaving only your half-way
solution with unprivileged accounts to prevent a complete compromise of
the system.
Are you starting to understand yet? Your code was secure because you
used a parameterized query, NOT because you used a stored procedure. If
you had used a parameterized query without a stored procedure, it would
have been equally secure. If you had used a stored procedure without a
parameterized query, it would have been broken. Stored procedures don't
prevent you from writing secure code, but they are completely irrelevant
to whether you write secure code or not.
--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation
L
Lee Fesperman
steve said:
My, my, you do like bluster. However, you did respond to my request for an explanation.
Let's see if you can back up that tough talk...
He only mentioned Oracle after I queried him about it.
Agreed, that looks good to me.
That has nothing to do with the code you posted above. But, anyway...
I don't know that non-standard syntax (does Oracle support that?) I can quess at two
meanings:
1) It works like ?=b, ?<>b where b is NULL. In SQL, either one would produce UNKNOWN
(treated as FALSE). So, no problem here.
2) It works like (or you really meant) ? IS NULL or ? IS NOT NULL. Since ? can't be NULL
(except by egregious programmer error ... more below), only ? IS NOT NULL would be a
problem. However, that is also an egregious programmer error which just as well could
happen in the stored procedure. Yeah, I know that stored procedures are often more
carefully crafted, but that is no guarantee. I notice you also didn't advise against
stupid mistakes.
If neither conjecture is right, please explain the meaning of the constructs.
This is mangled Java and will not compile. I have to guess again as to your meaning.
Are you saying that placing a single/double quote in the value passed for a ? parameter
will cause Oracle to misbehave? I am surprised. Either Oracle doesn't provide 'native'
support for prepared statements, or it is bady broken. Which is it, and how does a
stored procedure avoid the same problem?
So far, your grade is 'poor'. Note also that I said nothing about a perfect system.
I'm not aware of that horrible flaw. What does that have to do with prepared statements?
Oh, I see, you're talking about privileges. I'll touch on that below.
Obviously, the external user has privileges to call stored procedures. Thus, your
solution is to simply obfuscate names. You could do the same to table names.
This is not to say I am opposed to stored procedures. They do fill in some very obvious
weaknesses in SQL. They can contain processing that doesn't belong in the client, but
those issues were not mentioned by the OP.
Stored procedures have their disadvantages. They are often proprietary and obtuse. They
require knowledge of yet another language besides Java and SQL. That can introduce more
coding errors and decimate any hope for portability.
K
Kevin McMurtrie
[snip]
I assume you meant "rep_index=null".
At worst that casts both null and the column to an assumed type then
performs the comparison. It could be a slow index scan but nothing has
been injected.
Same deal.
That won't compile, but what you're implying doesn't inject anything.
PreparedStatement parameter values are safe, even capable of taking
binary strings. Read the JDBC specification.
Two ways to maybe cause a full index scan but no unauthorized data has
been returned or added. In other words, no injection attack.
I hate superstitious coders. Rather than read specifications and test
code in a source-level debugger, they invent invalid facts to support
limited observations. Their code becomes a mess of superstitious hacks
that performs poorly, is as brittle as glass, and can't be read by
anyone.
You're full of it. Getting the password from a JDBC connection is
likely possible but it can't be hacked out with a special
PreparedStatement bind value.
Good concluding advice.
I assume you meant "rep_index=null".
At worst that casts both null and the column to an assumed type then
performs the comparison. It could be a slow index scan but nothing has
been injected.
Same deal.
That won't compile, but what you're implying doesn't inject anything.
PreparedStatement parameter values are safe, even capable of taking
binary strings. Read the JDBC specification.
Two ways to maybe cause a full index scan but no unauthorized data has
been returned or added. In other words, no injection attack.
I hate superstitious coders. Rather than read specifications and test
code in a source-level debugger, they invent invalid facts to support
limited observations. Their code becomes a mess of superstitious hacks
that performs poorly, is as brittle as glass, and can't be read by
anyone.
You're full of it. Getting the password from a JDBC connection is
likely possible but it can't be hacked out with a special
PreparedStatement bind value.
Good concluding advice.
L
Lee Fesperman
Chris said:
Those constructs are not standard SQL (see my reply). Technically, they would yield
UNKNOWN not NULL, if supported. And as you say, they are useless. Can you indicate where
they are defined and in which ANSI standard?
I appreciate the effort, but I don't need any help with that guy, as you see ;^)
C
Chris Smith
Lee Fesperman said:
I can't point to a specific section of an ANSI specification. In fact,
if you know of a place where the ANSI SQL92 or SQL99 specs are publicly
available, that would be interesting. My understanding is that ANSI
holds the copyright and does not permit distribution.
I am instead making reference to Martin Gruber's "SQL Instant
Reference" and to observed behavior from PostgreSQL, which tends to be
at least approaching compliant with SQL92 (unlike some other free
databases...
.I'm curious, though. You seem to be implying that there is a difference
between "? = NULL" and "? = b" where b is NULL. Is there some reason
that the literal NULL is not allowed there? Was it disallowed
intentionally to prevent mistakes in SQL by people who don't understand
tri-value logic (and if so, doesn't it just invite subtler and more
difficult bugs?)
I'm not trying to help. I'm participating in a discussion on a public
forum. Sorry if you feel condescended to, or something like that. It's
not my intent.
--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation