How can I pass a parameter in an sql query without making it explicit in the query itself?

I’m testing my ignite cache, inserting a simple User in the cache and then through a query I get the User entered previously returned I have a problem writing the query. I noticed that writing like this:

@Test
    @EnableStreamCacheTestUtil(caches = {@Cache(name = "User", queryEntity = {@QueryEntity(tableName = "USER", keyClass = String.class, valueClass = User.class)})})
    public void Test_expected_Ok() throws Exception {
        Ignite ignite = Ignition.ignite();


        UsersRequest usersRequest = new UsersRequest();
        usersRequest.setName(TestCostants.NAME);
   

        IgniteCache<String, User> igniteCache = ignite.cache("User");
        User user = new User("Ennio","Ragno",90")
        igniteCache.put("1", user);
    

        SqlFieldsQuery sql = new SqlFieldsQuery("SELECT * " +
                " FROM USER WHERE NAME ='ENNIO'");

        int counter = 0;
        List<List<?>> cursor = igniteCache.query(sql).getAll();
        // try(QueryCursor<List<?>> cursor = igniteCache.query(sql)) {
        for (List<?> row : cursor) {
            log.info("SQLQUeryresult:{}", row);
            counter++;
        }
        Assertions.assertEquals(1,counter);
      }

    
    }

The test works fine, but if I write the query like this

        //Clearly in the constant that there is the value "ENNIO"
        UsersRequest usersRequest = new UsersRequest();
        usersRequest.setName(TestCostants.NAME);

        //some code

       
        SqlFieldsQuery sql = new SqlFieldsQuery("SELECT * " +
                " FROM USER WHERE NAME = "+usersRequest.getName());

Now it doesn’t work anymore. This is my first time trying to use queries and caches, but technically, shouldn’t it be the same?Is there a different way to write the query when passing parameters like this?

Answer

The correct way is to use parameter substitution, avoid SQL injection and other troubles:

    //Clearly in the constant that there is the value "ENNIO"
    UsersRequest usersRequest = new UsersRequest();
    usersRequest.setName(TestCostants.NAME);

    //some code

   
    SqlFieldsQuery sql = new SqlFieldsQuery("SELECT * " +
            " FROM USER WHERE NAME = ?").setArgs(usersRequest.getName());

Advertisement

Answer