시간과 공간 그리고 진실의 방

Issue finding - Information gathering

Caused by: java.sql.SQLSyntaxErrorException: FUNCTION moyamo_db.intersects does not exist
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
        ... 179 common frames omitted

expected intersects function does not exists.

Details collision point tracking

public interface TagRepository extends JpaRepository<Tag, Long> {

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    @Query("select c from Comment c where c.id = :id")
    Optional<Tag> findLockOnly(@Param("id") long id);

    Optional<Tag> findByName(String name);

    @Query(value = "select t from Tag t where intersects(t.geometry, :area) = true and t.tagType = 2 and t.visibility = 0")
    List<Tag> findByLocation(@Param("area") Geometry area);

    @Query(value = "select t.id from Tag t where intersects(t.geometry, :area) = true and t.tagType = 2 and t.visibility = 0")
    List<Long> findIdByLocation(@Param("area") Geometry area);

	List<Tag> findByNameLike(String name, Pageable pageable);

    List<Tag> findByPlantId(Long plantId);

    @Modifying
    @Query(value = "delete from Tag t where t.plantId = :plantId")
    void deleteByPlantId(@Param("plantId") Long plantId);
}

it could be caused that There is no Funtion, called interesect after updating Aurora 5.7 to 8.0. Let's make function 

DELIMITER //

CREATE FUNCTION intersects(geom1 GEOMETRY, geom2 GEOMETRY) 
RETURNS BOOLEAN
BEGIN
    RETURN ST_Intersects(geom1, geom2);
END //

DELIMITER ;

And we will take Geometry spatial data.

Fix Query like below

@Query(value = "select t from Tag t where ST_Intersects(t.geometry, :area) = true and t.tagType = 2 and t.visibility = 0")
List<Tag> findByLocation(@Param("area") Geometry area);

@Query(value = "select t.id from Tag t where ST_Intersects(t.geometry, :area) = true and t.tagType = 2 and t.visibility = 0")
List<Long> findIdByLocation(@Param("area") Geometry area);

            // set gps
            Pageable pageWithSort = PageRequest.of(offset, pageSize, Sort.by("createdAt"));

Pageable support "WithSort" method and properties. Quote Below

https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/domain/Sort.html

withSort

public PageRequest withSort(Sort.Direction direction,
 String... properties)

Creates a new PageRequest with Sort.Direction and properties applied.

Parameters:
direction - must not be null.
properties - must not be null.

Returns:
a new PageRequest.

Example Code

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
                .cors().configurationSource(request -> {
                    CorsConfiguration config = new CorsConfiguration();
                    config.setAllowedOrigins(Collections.singletonList("*")); // Allow localhost
                    config.setAllowedMethods(Arrays.asList("GET", "POST", "PUT", "DELETE"));
                    config.setAllowedHeaders(Arrays.asList("Authorization", "Content-Type"));
                    config.setAllowCredentials(false);
                    return config;
                })
                .and()
                .csrf().disable()
                .sessionManagement().sessionCreationPolicy(SessionCreationPolicy.STATELESS)
                .and()
                .httpBasic()
                .authenticationEntryPoint(customAuthenticationEntryPoint)
                .and()
                .exceptionHandling().accessDeniedHandler(customAccessDeniedHandler)
                .and()
                .authorizeRequests()
                .antMatchers("/signup").permitAll()
                .antMatchers("/login").permitAll()
                .anyRequest().authenticated();

        http.addFilterBefore(jwtRequestFilter, UsernamePasswordAuthenticationFilter.class);
    }

In example, setAllowCredentials method was seen "false". if want to set "setAllowedOrigins" method for asteroid, means '*', it need to be "false".

if not, will see next issue.

ev-api-1  | java.lang.IllegalArgumentException: When allowCredentials is true, allowedOrigins cannot contain the special value "*" since that cannot be set on the "Access-Control-Allow-Origin" response header. To allow credentials to a set of origins, list them explicitly or consider using "allowedOriginPatterns" instead.
ev-api-1  | 	at org.springframework.web.cors.CorsConfiguration.validateAllowCredentials(CorsConfiguration.java:495) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.cors.CorsConfiguration.checkOrigin(CorsConfiguration.java:599) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.cors.DefaultCorsProcessor.checkOrigin(DefaultCorsProcessor.java:174) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.cors.DefaultCorsProcessor.handleInternal(DefaultCorsProcessor.java:116) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.cors.DefaultCorsProcessor.processRequest(DefaultCorsProcessor.java:95) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:87) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:112) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:82) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.security.web.session.DisableEncodeUrlFilter.doFilterInternal(DisableEncodeUrlFilter.java:42) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:221) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:186) ~[spring-security-web-5.7.11.jar!/:5.7.11]
ev-api-1  | 	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:354) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:267) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.30.jar!/:5.3.30]
ev-api-1  | 	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:926) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1790) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.80.jar!/:na]
ev-api-1  | 	at java.base/java.lang.Thread.run(Thread.java:840) ~[na:na]

Working with APIs, particularly those that use the JWT model for authentication, can be challenging because it requires copying and pasting the Bearer token into the Authorization headers.

The blog post explains how to reuse JSON web tokens in Postman, which will speed up your API Testing experience.

Prerequisite

Basic HTTP and RestAPI Understanding

Let's create an environment that allows us to change the context of our requests.

🎯 Select the Environment quick look option.

Click on the add button

🎯 Set the name of the Environment [green], the base URL, and click the save button

🎯 Change the Environment to the newly set one and also the baseUrl in two curly brackets

🎯 Hover over the baseUrl to see the URL referenced from the environment variables.

🎯 Following that, we set our JWT against the response token.

🎯 Try the request again, everything should still work

If you open the environment quick look section, the new JWT token should be listed as one of the variables.

🎯 To use the JWT on protected routes, follow the highlighted points.

Finally, if you open the headers tab, you will notice that the Authorization values are automatically prefilled.

Conclusion

I hope this post was useful and has shortened your API testing time. Thank you for reading.