When generating SQL statements with sprintf (),% can be escaped with%

#Introduction I’m currently working for a company doing full scratch development with PHP + MySQL. It’s been about a month since I joined the company, so most of the time I read existing code.

When we generate SQL statements in php files I’m trying to format it using sprintf (). (I don’t know if this is standard)

Among them, while examining the description that I do not understand well I came across an interesting rule, so I’ll leave Qiita so I don’t forget it.

When making a SQL ambiguous search query with #sprintf ()

$ freeword ='qiita';

$ sql = sprintf ('SELECT * FROM title LIKE "%%% s %%"', $ freeword);

In this code, the second argument $ freeword of sprintf () is in the “% %% s %%” part of the first argument. It is replaced and formatted, but there are some rules to be aware of when doing so.

First, “% s” in the middle is the value of the variable, which is converted to qiita in this example. So, at this point, it will be “%% qiita %%”.

At this stage, the author “A lot of% in vain, right?” I misunderstood

In the first place, the symbol% is not recognized as a character by sprintf (). Must be escaped to character.

So the symbol for escaping% is%.

#In other words

Of the remaining “%”, the latter% is escaped with the front “%”, resulting in a “character string” called just “%”.

①% → “%” qiita% → “%”

② Result “% qiita%” remains

③ As a result, this remains in the SQL LIKE statement. $ sql ='SELECT * FROM title LIKE% qiita%'; Completion of the SQL statement.

#Easy-to-understand reference article

The explanation of the following blog article was very easy for beginners to understand I quoted it for most of the article creation.

・ Hara-chan’s blog

Thank you very much.