MyBatis String as Parameter

Question

I want to use a String parameter for a Select Statement in MyBatis. My mapper.xml:

<select id="selectAll" parameterType="String" resultMap="fastXMLResultMap">
        SELECT CREATIONDATE, DOCUMENTID, TITEL, REGTITEL, INFORCEDATE, DOCTYPE
        FROM #{databBaseTable}
</select>

And the calling function:

public List<FastXMLObject> selectAll(String databBaseTable) {

    SqlSession session = sqlSessionFactory.openSession();

    System.out.println("Table: "+databBaseTable);

    try {
        List<FastXMLObject> list = session.selectList("FastXMLObject.selectAll",databBaseTable);
        return list;
    } finally {
        session.close();
    }
}

The string dataBaseTable is the name of the table of my database (who would have thought that) because I want to get data dynamically from verious tables.

But unfortunatelly this does not work: Error: ORA-00903: Ungültiger Tabellenname (invalid table name) but it isnt. When I print out the value of "databBaseTable" it is the exact name of the table. And when I write the name of the table to my mapper.xml without a variable it works. What do I do wrong?

you can find the working example [here][1] [1]: http://stackoverflow.com/a/43357454/2762716 — Alexander Davliatov, Apr 11 '17 at 22:51

score 34 · Accepted Answer · answered Aug 01 '12 at 13:31

34

Use ${dataBaseTable} instead of '#'. The difference is that '#' is used for PreparedStatement substitution. '$' is for direct String substitution.

However, if you do this, you can't pass the table name in as a parameter to the selectList() call. You need to set the table name as a property. Properties can be set by using the <properties> element in the MyBatis config.xml or directly in code by using Configuration.getVariables().

See the 'String Substitution' section in the MyBatis Docs.

answered Aug 01 '12 at 13:31

AngerClown

6,149
1
25
28

Ok I know what you mean but I just cant really figure out what I have to do. I should add ad property to my config.xml like: ` ` But what should be my ressource and whats the value? I want to set the value dynamically. Sorry but I am totally new to this topic. – Metalhead89 Aug 01 '12 at 13:38
`` loads a properties file. `...` puts the value directly into the MyBatis config xml. How dynamic do you need the value to be? Just at startup or for each DB call? – AngerClown Aug 02 '12 at 00:14
Yes, i am traversing a directory with many subdirectory and each subdirectory represents a table name of the database. So The select From statement has to be totally dynamic. As I wrote below I could figure it out with ${value} but if you know how to put more then one String parameter to the calling function it would be nice, if also not neccesary by now. – Metalhead89 Aug 02 '12 at 06:51
Dude.. Thanks for saving my life.. I was almost about to die just to figure that out why its not working !! :( I didn't notice that I am dealing with table and not its data. – Aman Gupta Jan 29 '15 at 07:49
Usage of ${dataBaseTable} directly can render your code open to injection, if you are not sanitising the input. – bub Jun 03 '16 at 12:54

score 6 · Answer 2 · answered Aug 01 '12 at 13:52

6

Ok I definitely do not know why this works but I just used the following to solve the problem:

<select id="selectAll" parameterType="String" resultMap="fastXMLResultMap">
        SELECT CREATIONDATE, DOCUMENTID, TITEL, REGTITEL, INFORCEDATE, DOCTYPE
        FROM ${value}
</select>

I did not set any properties or something else, it was just the change from FROM #{databBaseTable} to FROM ${value}

I someone could answer why this works would be nice. But for now this really helped me.

answered Aug 01 '12 at 13:52

Metalhead89

1,740
9
30
58

12

Dude, maybe you should give the credit to AngerClown ?! – Nabil Sham Feb 10 '14 at 05:59
Nabil-Sham - Yes, @AngerClown s answer should be accepted. – bub Jun 03 '16 at 12:58

johnm · Answer 3 · 2015-11-10T12:37:59.827

5

With the #{..} syntax MyBatis uses a jdbc PreparedStatement object upon which you cannot specify the table name as a parameter. With #{..} you can only parameterize the parameters of the sql statement.

When you use ${..} syntax MyBatis does plain old string substition so your free to parameterize pretty much any part of the sql you desire.

Note: as a by the way: with #{..} you are pretty safe from sql injection, but with ${..} it does open the door to such attacks

edited Nov 10 '15 at 12:37

answered May 23 '13 at 09:49

johnm

7,327
1
24
35

Could you point out in which lines myBatis PreparedStatement inject the value to those `#{...}`s? I'm learning from myBatis source code... – NeoZoom.lua Dec 25 '19 at 12:44

MyBatis String as Parameter

3 Answers3

Linked