Problem with using an Apostrophe in a string for an SQL command

English
#1

I have the following string placed in a variable named [sName]:
.
Yura_(Girl's_Day)
.
Another variable named [sPhrase] is initialized with the following content:
.
sPhrase = "https://asianwiki.com/" & sName
.
This variable [sPhrase] is part of the following SQL command which is placed in the variable named [sSQL]:

sSQL = "UPDATE ""TActrices"" SET ""LinkAsianWiki"" = '" & sPhrase & "' WHERE ""ActriceID"" = " & sRecordID
.
This command is executed using these lines of code:

oConnect = ThisDatabaseDocument.CurrentController.ActiveConnection 'Connection DB
oStatement = oConnect.createStatement()
oStatement.executeQuery(sSQL)

.
However, the content was entered in the “LinkAsianWiki” field for the current record, but incompletely; the string entered in the field is: https://asianwiki.com/Yura_(Girl .
.
What is even more curious is that all the form records for this field have been modified by this content. So all previous content has been replaced.
.
Is there a problem with using an Apostrophe in a string for an SQL command?

#2

In SQL, in a string literal (text enclosed in apostrophes), the apostrophe must be doubled.
The easiest way in your design is to write instead of sPhrase

Replace(sPhrase, "'", "''")
1 Like
#3

@sokol92,
,
What you are suggesting works in the sense that what will definitely be written in the table field is this: https://asianwiki.com/Yura_(Girl’s_Day). When I click to access this page from a button provided for this purpose, the correct page opens.
,
However, during the procedure of creating the URL, I run the SQL command through an UNO service to ensure that the URL is indeed functional. Here it doesn’t work; it’s not the right page that is displayed, but rather a page of the site indicating that I did not access the right page. A page is displayed indicating that the page Yura_(Girl''s_Day) does not exist.
,
It is as if the command createUnoService("com.sun.star.system.SystemShellExecute").execute(sPhrase, "", 0) was unable to find the sPhrase WEB page.
Oddly enough, it works with any other URL that doesn’t contain an apostrophe.
,
However once entered in the field as mentioned before. The command createUnoService("com.sun.star.system.SystemShellExecute").execute(sHyperLink, "", 0) executed from a button provided for this purpose works.
,
Do you have an explanation for this?

#4

The URI protocol has its own rules. :slightly_smiling_face:
At the end of your address should be something like:

\Yura_%28Gir%27s_Day%29

In this case, replacing sPhrase with

  ConvertToUrl(sPhrase)

can help us.

#5

I finally found. The apostrophe has been changed in sPhrase as needed for the SQL command, double apostrophe. I simply moved the createUnoService command, taking care to re-modify sPhrase to have a single apostrophe. It works.
.

	oConnect = ThisDatabaseDocument.CurrentController.ActiveConnection	'	Connection BD
	oStatement = oConnect.createStatement()
	oStatement.executeQuery(sSQL)				

	sPhrase = Replace(sPhrase, "''", "'")	'	Formatage en cas de présence d'apostrophe
	createUnoService("com.sun.star.system.SystemShellExecute").execute(sPhrase, "", 0)	'	Service Uno permettant d'exécuter la commande SQL
#6

But always keep in mind: There are a lot of special chars out there:

#7

@Wanderer,
.
Regarding SQL, is there a list of special characters with a definition of their uses?
.
Similarly for URLs, what are the characters that cannot be used or that need to be replaced to ensure proper functioning?

#8

SQL: SQL 92. SQL databases can have their own dialects.

URL: RFC3986.

1 Like
#9

Depending on you query even OR is dangerous:

Sanitizing input is one approach, the other is prepared statements, and you are allowed to use both at the same time.

#10

THANKS! This site is quite interesting, there is a lot to learn. Excellent reference!

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.