[#13585] - Mssql - fix escape() and add support for unicode chars
- Fixed in Code Base
- 5 Feb 2017
- Medium
- Build: staging
- # 13585
- Diff
- csthomas:fixmssql3
User tests: Successful: Unsuccessful:
Pull Request for better version of #13534
Summary of Changes
- Fix escape sequences - see https://support.microsoft.com/en-us/kb/164291
- Repair
$db->escape() - stripslashes() added in #13534 is now not needed
- In
$db->quote()add prefix N, means instead column = 'text' there will be column = N'text' which add support for unicode, international chars (ex. Polish chars ąęćółńżź)
Testing Instructions
- Install fresh joomla staging, (if you have installed joomla with some modification in database then JSON Decode error may occur).
- Edit or add some article:
- change
Meta Descriptionfield to (add some national chars like ąęśżźół):
xęą
\
\
x
- change
Titleto "TEST 1 - ale[x]"
- Save and check result.
- You should see that
Meta Descriptionfield is not saved properly (new line has been removed, national chars disapears). - Edit template file at
templates/protostar/index.php - Add php code at the bottom of the file (before
</body>):
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true)
->select('title')
->from('#__content')
->where('title LIKE ' . $db->quote('%' . $db->escape('ale[x]', true) . '%', false));
$title = $db->setQuery($query)->loadResult() ?: 'NOT FOUND';
echo '<p>Result 1: "' . $title . '"</p>';
$query = $db->getQuery(true)
->select('COUNT(*)')
->from('#__content')
->where('title LIKE ' . $db->quote('%' . $db->escape('%%%', true) . '%', false));
echo '<p>Result 2: "' . $db->setQuery($query)->loadResult() . '"</p>';
?>- Go to front page and check results:
- Result 1: "NOT FOUND"
- Result 2: "67"
or similar (number bigger than 0) - sample testing data installed
- Apply current PR by PatchTester.
- Repeat point 2,3,4. Now
Meta Descriptionis saved OK. - Set
Content Rightsfield (json column) to:
Author\"\
rights.
Note: If you added above text before applying patch you get Json error on joomla with applied patch. Before adding patch please reset that field.
11. Save and check whether content rights is correct.
12. Go to front page and check results:
- Result 1: "TEST 1 - ale[x]"
- Result 2: "0"
Explanation for tests:
- %%% - there is no article with text '%%%' and should not found any articles. If found then
escapedoes not work if correct way. - ale[x] - should find one article because there is one title with these chars. But before patch it treats brackets as a special chars and try to find only "alex".
- backslash at the end of line test fix for https://support.microsoft.com/en-us/kb/164291
Content Rights- is stored, encoded to json. This test check whether json which contains backslashes or quotes works correctly.
Documentation Changes Required
N/A
| Status | New | ⇒ | Pending |
joomla-cms-bot
- | Category | ⇒ | MS SQL Libraries |
| Labels |
Added:
?
|
||
This is OK, you have in total 67 articles, I had 75 articles.
sql LIKE '%%%' found al your articles.
wait a moment
You talk about Result 2: "67" where I got 75? Right?
No, I talk about Result 1. My results a different then yours.
Last time I added unicode support, it may change that. I'm checking it.
Do you have title of article "TEST 1 - ale[x]" <- brackets are important to test it.
Result as you get - I think that you may forget add brackets in article title. If not I will search more.
I do have the brackets in the title.
which database do you use: local SQL Server or Azure? If SQL server then what version?
SQL Server 2008 R2
| Title |
|
||||||
I have rebased PR and improved a test instruction.
But there is no changes that could return your result.
I based on https://msdn.microsoft.com/library/ms179859.aspx where [ has to be escaped like [[].
The same is for % and _.
I will wait for next test. May it resolve something.
It would be a good idea to rebase this now that AppVeyor is CI testing our unit test suite with MSSQL
@csthomas can you comment on the unit test failure on the Framework version?
https://ci.appveyor.com/project/photodude/database/build/1.0.67/job/l64qi7ws1j9ghi8b#L898
Do we just need to change the unit test to have brackets around the % char? % vs [%]
For mssql LIKE clause you have to escape 3 chars: _,% and [ because it is default escape char.
% -> [%]
_ -> [_]
[ -> [[]
| Category | MS SQL Libraries | ⇒ | MS SQL Libraries Unit Tests |
Now AppVeyor is happy:)
I have tested this item
I retested your patch with a new checkout and now I get your results. One thing that´s obviously different to your environment is, that I get a 404 component not found error in frontend with no testing data installed. So I am curious: what database version are you using?
This comment was created with the J!Tracker Application at issues.joomla.org/tracker/joomla-cms/13585.
Not installed sample data probably is a reason
but IIRC every article without set up publish_down is treats on mssql as expired (datetime type should be change to datetime2 - for now joomla is waiting for that)
My system is linux with installed mssql on it.
As I have no sample data installed the difference most likely is the runtime environment. I will dig into it when I have more time.
Given this only affects SQLServer I'm going to merge this with the code review and the one good test
| Status | Pending | ⇒ | Fixed in Code Base |
| Closed_Date | 0000-00-00 00:00:00 | ⇒ | 2017-02-05 19:41:25 |
| Closed_By | ⇒ | wilsonge | |
| Labels |
Added:
?
|
||
I have different results for step 7 and step 12:
Step 7:
Result 1: "TEST 1 - ale[x]"
"67"
Step12:
Result 1: "NOT FOUND"
Result 2: "0"
The rest works as described.