Безопасный полнотекстовый поиск в базе данных Joomla
рейтинг: 8.6/10, голосов: 7
Часто приходится писать расширения, которые используют полнотекстовый поиск по базе данных. Один из таких компонентов — это поиск по сайту. Тут нельзя обойтись простым экранированием, а строка может содержать специфические символы. Для этих целей обычно пользуются LIKE — что очень не безопасно. Попробуем разобраться, как уменьшить риск полнотекстового поиска по базе данных Joomla.
Но как не крути конструкцию LIKE %…% приходится использовать для поиска по базе данных Joomla. Некоторая сложность возникает если мы попытаемся искать специальные символы, который по идее нужно экранировать перед использованием в MySQL-запросах. Существует два основных метода класса JDatabase, которые призваны помочь нам: getEscaped() — для экранирования спец символов и Quote() для обрамления кавычками. Например
// получаем объект базы данных
$db =& JFactory::getDBO();
// подготовка строки для поиска
$escaped = $db->getEscaped($searchFor, true);
$quoted = $db->Quote('%' . $escaped . '%', false);
// собираем запрос
$like = $db->nameQuote('columnName') . ' LIKE ' . $quoted;
Обычно, когда мы используем JDatabase:Quote(), в нашей строке автоматически экранируются специальные символы. Такие символы как «%" (процент) и «_» (подчеркивание) тоже будут верно экранированы при использовании второго параметра (в примере выше он установлен на «false»). Если это не сделать, то запрос будет выполнен с ошибкой. Для того чтобы верно использовать эту строку воспользуемся методом JDatabase:getEscaped(), в качестве второго параметра которого принимается необязательный параметр, который указывает были ли экранированы спец символы «%" и «_». После этого собираем запрос полностью, обрамляя обработанные строки знаком «%», кавычками и формируем весь запрос.
Для того чтобы более яснее разобрать что произошло с переменными, предлагаем вам ознакомиться с таблицей ниже.
| Имя переменой | Значение переменной |
| $searchFor |
We are 25% through the_chapter |
| $escaped |
We are 25\% through the\_chapter |
| $quoted |
'%We are 25\% through the\_chapter%' |
| $like |
'columnName' LIKE '%We are 25\% through the\_chapter%' |
Пример можно сделать более гибким, если заменить все пробелы символом «%» (напомним, что в контексте MySQL он обозначает любое кол-во символов). В итоге получаем код следующего вида:
// разделяем исходную строку по пробельным символам
$words = preg_split('~[\s,\.]+~', $searchFor);
// перебираем слова, и экранируем в каждом из них символы
for ($i = 0; $i < count($words); $i ++) {
$words[$i] = $db->getEscaped($words[$i], true);
}
// собираем массив в строку и обрамляем полученную строку кавычками
$quotedSearch = implode('%', $words);
$quotedSearch = $db->Quote('%' . $quotedSearch . '%', false);
// собираем готовый sql
$like = $db->nameQuote('columnName') . ' LIKE ' . $quoted;
Посмотрим значения переменных из этого листинга во время выполнения кода.
| Имя переменой | Значение переменной |
| $searchFor |
We are 25% through the_chapter |
| $words |
array('We', 'are', '25%', 'through', 'the_chapter') |
| $words |
array('We', 'are', '25\%', 'through', 'the\_chapter') |
| $quotedSearch |
We%are%25\%%through%the\_chapter |
| $quotedSearch |
'%We%are%25\%%through%the\_chapter%' |
| $like |
'columnName' LIKE '%We%are%25\%%through%the\_chapter%' |
vgngn
Hello to all, how is all, I think every one is getting more from this
website, and your views are pleasant in favor of new viewers.
Hello to every one, since I am truly keen of reading this web site's post to be updated regularly.
It contains good stuff.
Hello there I am so happy I found your website, I really
found you by error, while I was looking on Google
for something else, Regardless I am here now and would just like to say thank you
for a tremendous post and a all round thrilling blog (I also love the theme/design), I
don’t have time to look over it all at the minute but I have
bookmarked it and also added in your RSS feeds, so when I have
time I will be back to read much more, Please do keep up the superb jo.
Hello, Neat post. There is an issue together with your web site in web explorer, might check this?
IE still is the marketplace leader and a huge component
of other folks will miss your magnificent writing due to this problem.
Great delivery. Great arguments. Keep up the
great spirit.
Great goods from you, man. I have take note your stuff prior to and you're simply extremely fantastic.
I actually like what you've received right here, really like what you
are saying and the best way through which you are saying it.
You're making it entertaining and you still take care of to stay it
smart. I cant wait to read far more from you. That is really a tremendous website.
I'm curious to find out what blog system you're working with?
I'm experiencing some small security issues with my latest blog
and I would like to find something more safe.
Do you have any recommendations?
Great goods from you, man. I have have in mind your stuff prior to and you are just too wonderful.
I actually like what you have bought here, certainly like what you are
stating and the best way in which you are saying it.
You are making it enjoyable and you still take care of to stay it sensible.
I can not wait to read much more from you. That is really a wonderful site.
Appreciation to my father who shared with me concerning this web site, this blog is
actually remarkable.
Hello, I enjoy reading all of your article post. I like to write a little comment to
support you.
always i used to read smaller posts which also clear their motive, and
that is also happening with this post which I am
reading now.
Hello there! This blog post could not be written any better!
Going through this post reminds me of my previous
roommate! He always kept preaching about this. I'll forward this post to him.
Pretty sure he will have a great read. I appreciate you for sharing!
Hi, always i used to check webpage posts here early in the dawn, since i enjoy to gain knowledge
of more and more.
I'm truly enjoying the design and layout of your blog.
It's a very easy on the eyes which makes it much more enjoyable
for me to come here and visit more often. Did you hire out a developer to create your theme?
Exceptional work!
Ahaa, its good conversation on the topic of this piece of writing at this place at this weblog, I have read all
that, so at this time me also commenting here.
Unquestionably believe that which you stated. Your favorite
justification seemed to be on the internet the easiest thing to be aware of.
I say to you, I certainly get annoyed while people think about worries that they just do not know about.
You managed to hit the nail upon the top and defined out
the whole thing without having side effect , people could take a signal.
Will probably be back to get more. Thanks
I'm curious to find out what blog platform you have been working with?
I'm having some minor security problems with my
latest site and I would like to find something more risk-free.
Do you have any recommendations?
I am no longer positive the place you are
getting your information, but great topic. I must spend some time studying more or understanding more.
Thanks for wonderful information I was searching for this
information for my mission.
Hi! Someone in my Myspace group shared this site with
us so I came to check it out. I'm definitely enjoying the information. I'm bookmarking
and will be tweeting this to my followers! Outstanding blog and amazing design.
Excellent post but I was wondering if you could write a
litte more on this subject? I'd be very thankful if you could elaborate a little
bit further. Cheers!
Hi there, I enjoy reading through your article. I wanted to write a
little comment to support you.
I read this article completely concerning the resemblance of hottest and
earlier technologies, it's amazing article.
I visited many sites but the audio quality for audio songs existing at this web
page is truly wonderful.
Excellent site you have here but I was curious if you knew of any user
discussion forums that cover the same topics
talked about in this article? I'd really love to be a part of online community where I can get advice from other experienced individuals that share the same interest.
If you have any recommendations, please let me know.
Kudos!
At this moment I am going away to do my breakfast, when having my breakfast coming again to read more news.
whoah this weblog is great i like reading your articles.
Keep up the great work! You understand, lots of people are searching around
for this information, you could aid them greatly.
Hi! This post couldn't be written any better! Reading this post reminds me of my
good old room mate! He always kept chatting about this.
I will forward this article to him. Pretty sure he will have a good read.
Thank you for sharing!
My spouse and I stumbled over here coming from a different website and
thought I might check things out. I like what I see so i am just following you.
Look forward to looking into your web page repeatedly.
magnificent issues altogether, you simply received a emblem new reader.
What may you recommend in regards to your post that you simply made some days in the past?
Any certain?
Good post. I learn something new and challenging on sites I stumbleupon everyday.
It's always useful to read through content from other writers and use a little
something from their sites.
I was suggested this blog by my cousin. I am not sure
whether this post is written by him as no one else know such detailed about
my trouble. You are wonderful! Thanks!
Great post. I used to be checking continuously this blog and I'm inspired!
Extremely useful info particularly the ultimate phase :) I handle such information much.
I used to be seeking this particular info for a very long time.
Thank you and good luck.
Hello, yup this article is really fastidious and I have learned lot of things from
it about blogging. thanks.
It's awesome to visit this web page and reading the views of all friends on the topic of this piece of writing,
while I am also keen of getting experience.
This information is priceless. How can I find out more?
Great beat ! I wish to apprentice even as you amend your website, how could i subscribe
for a blog web site? The account helped me a appropriate deal.
I had been a little bit acquainted of this your broadcast provided
bright clear concept
Wonderful goods from you, man. I have understand your
stuff previous to and you're just too wonderful.
I really like what you have acquired here, really like what you're saying
and the way in which you say it. You make it entertaining and you
still care for to keep it wise. I can't wait to read far more from you.
This is really a wonderful site.
It's a pity you don't have a donate button! I'd without
a doubt donate to this fantastic blog! I suppose for now i'll settle for bookmarking and
adding your RSS feed to my Google account. I look forward to brand new updates and
will talk about this site with my Facebook group. Chat soon!
Hey! I just wanted to ask if you ever have any problems with hackers?
My last blog (wordpress) was hacked and I ended up losing
months of hard work due to no backup. Do you have any
methods to protect against hackers?
I was pretty pleased to discover this page.
I need to to thank you for your time due to this fantastic read!!
I definitely really liked every part of it and I have you book marked to check out new things in your blog.
Tremendous issues here. I am very satisfied to see
your article. Thanks a lot and I am looking forward to touch you.
Will you please drop me a mail?
I do not know if it's just me or if everyone else experiencing problems with your site.
It appears as if some of the text within your posts are running off
the screen. Can somebody else please provide feedback and let me
know if this is happening to them too? This might
be a issue with my browser because I've had this happen previously.
Many thanks
If some one needs expert view about running a blog
then i propose him/her to pay a quick visit this web
site, Keep up the nice job.
Fantastic web site. Plenty of helpful information here. I am sending it
to several buddies ans also sharing in delicious.
And obviously, thank you on your effort!
That is really attention-grabbing, You're an overly skilled blogger.
I've joined your rss feed and stay up for seeking more of your wonderful post.
Also, I have shared your web site in my social networks
hello there and thank you for your info – I have definitely picked
up anything new from right here. I did however expertise a few technical points using
this site, since I experienced to reload the site a lot of times previous to I could get it to
load properly. I had been wondering if your web hosting is OK?
Not that I am complaining, but sluggish loading instances
times will often affect your placement in google and can damage your quality score if advertising and marketing with
Adwords. Anyway I am adding this RSS to my e-mail and could look
out for a lot more of your respective interesting content.
Ensure that you update this again soon.
My spouse and I stumbled over here by a different web
address and thought I might as well check things out. I like what I see so now i am following you.
Look forward to exploring your web page again.
Hi! This is kind of off topic but I need some guidance from an established blog.
Is it very difficult to set up your own blog?
I'm not very techincal but I can figure things out pretty
fast. I'm thinking about making my own but I'm not sure
where to start. Do you have any tips or suggestions? Thanks
I am really thankful to the holder of this website who has shared this
wonderful piece of writing at at this time.
Hello would you mind letting me know which web host you're using?
I've loaded your blog in 3 different internet browsers and I
must say this blog loads a lot faster then most. Can you
recommend a good hosting provider at a fair price? Thanks, I appreciate it!
I got this website from my buddy who told me concerning this web site and now this time
I am browsing this web page and reading very informative posts here.
I'm excited to find this page. I wanted to thank you for ones time for this fantastic read!!
I definitely really liked every little bit of it and i also have
you book-marked to look at new things in your web site.
Hello mates, how is all, and what you desire to say about this post, in my view its really amazing designed for me.
certainly like your web site however you need to take a look at
the spelling on several of your posts. Several of them are rife with spelling
issues and I find it very troublesome to tell the truth nevertheless I'll certainly come back again.
I constantly emailed this website post page to all my contacts, for the reason that if like to read it then my friends will too.
adreamoftrains website hosting companies