How to make a LIKE IN equivalent to PROCV

Asked

Viewed 52 times

3

I have the following question (below is just an example, because I can not send the true info) I want to make a where where I find the name of the social reason by the fancy name of the establishment, but as the names are similar and not equal there is no way to be by IN However like this where would be by a subquery I cannot do by LIKE would like to know how it should do.

I would like to know what is equivalent to a LIKE IN

Follow examples of querys below:

Example query 1:

SELECT RAZAO_SOCIAL FROM TABELA_01

being RAZAO_SOCIAL = ANA’S SUPERMARKET

Example QUERY 2:

SELECT NOME_FANTASIA FROM TABELA_02
 sendo NOME_FANTASIA = SUPER ANA

What I want

SELECT RAZAO_SOCIAL FROM TABELA_01
WHERE RAZAO_SOCIAL **LIKE IN** (SELECT DISTINCT NOME_FANTASIA FROM TABELA_02)
sql sql-server operator-like

1 answer

2

SELECT t1.razao_social,
       t2.nome_fantasia
  FROM tabela_01 t1
 INNER JOIN tabela_02 t2
    ON t2.nome_fantasia LIKE '%' + t1.razao_social + '%'

  • If you have a registered "malicious" social reason that has not been validated (neutered) when it was inserted I think it has a chance to roll an SQL Injection here, no? In fact, already at the time of insertion.

  • @Piovezan do not know where he will use and how he will use the query. But anyway I am not using exec in razao_social, I don’t see how I could stand a chance.

  • I’m just raising the hare to keep in mind who will implement, really not knowing where it will use can not know. But when I think of a malicious string I always think of the idea of Bobby Tables https://bobby-tables.com/img/xkcd.png. that can happen when the execution of the external query is called.

  • @Sorack I think that way it doesn’t catch if the fantasy is "SUPER ANA" where the reason is "ANA SUPERMARKET" I think you’ll have to break by words and do the like in them, something like what you think?

  • @David I don’t know, but that way you already have an answer here in the O.R.

  • @Sorack The risk I see of SQL Injection is the string being for example Whatever'; DROP TABLE tabela_01; -- and have been called an exec (is that what executes queries?) in the external query. You will run three queries in a row that are separated by a semicolon, the first with a LIKE %Whatever', the second which is a DROP TABLE and the third that is basically ignored because it has a comment indicator that serves to ignore everything that comes after, in case the rest of the first query that is only the character %.

  • 1

    Tried for similarity of strings ? I already solved problem with this. https://stackoverflow.com/questions/2621739/similarity-between-strings-sql-server-2005

Show 2 more comments

Browser other questions tagged sql sql-server operator-like

You are not signed in. Login or sign up in order to post.