Re: VALUE MANIPULATION IN MySQL with JAVA question..
On 2009-03-19 09:16:49 -0400, Lew <noone@lewscanon.com> said:
justineee wrote:
public void insertValuesCred(String tablename)
{
insertToDb = "INSERT into "+tablename+" VALUES('"+getDate+"',
'0', '"+getCreditAmount+"', "+balance(tablename)+")";
insert(insertToDb);
}
http://xkcd.com/327/
Never use SQL this way.
Two other comments: SSCCE, and third-normal form.
Lew's right. _Never_ build SQL using string concatenation or variable
substitution. Consider:
PreparedStatement insertCredit =
conn.prepareStatement
("INSERT INTO credits VALUES (?, '0', ?, ?)");
try {
insertCredit.setDate (1, getDate);
insertCredit.setBigDecimal (2, getCreditAmount);
insertCredit.setBigDecimal (3, balance);
insertCredit.executeUpdate ();
} finally {
insertCredit.close ();
}
There is absolutely no chance for badly-formatted data to turn into an
SQL injection problem, because the database driver (as specified by the
JDBC spec) handles the parameters separately from the query itself,
including any quoting or escaping necessary.
It's worth noting that you are not permitted to use a ? placeholder for
table or column names: you can't portably prepareStatement ("SELECT *
FROM ?"), for example. You're expected to know in advance what your
queries are accessing. ORMs and other tools that generate SQL on the
fly go to great lengths to ensure the result is safely executable even
in the face of weird data. If for some reason you absolutely cannot
avoid dynamically-generated SQL, use iBatis, rather than writing it
yourself.
I also strongly object to the implied data model: one-table-per-ledger
will break down very badly as your number of ledgers grows, or if you
need to relate anything to ledger items. Use a table structure that
includes the ledger key in the rows, rather than in the table name, and
make it part of the table's primary key.
There are some performance advantages in certain situations to
physically sharding data across multiple tables - but the application
should never be aware of it. Use views and query rewriting, or stored
procedures, to map sharded physical data models to a uniform logical
data model that apps actually use.
-o