B4J Question How to avoid sending special chars to SQL

ThRuST

Well-Known Member
Licensed User
When working with data stored in mySQL and SQLite, it's not appropriate to store special chars in their original form
since that will cause errors when using PHP scripts on the server. I've noticed text from HTMLeditor works well
when storing into a database because all special chars are handled differently. So what's the appropriate procedure
to store special chars in a database? Do you need to enable UTF8 in PHP for this and where do you encode/decode
the special chars, before sending, in PHP at arrival or before processing it back to the application?
I have not seen a post covering this so this seems really important to cover so we all learn how this should be handled.

Example
& (ampersand) &
"
(double quote) ", unless ENT_NOQUOTES is set
' (single quote) ' (for ENT_HTML401) or ' (for ENT_XML1, ENT_XHTML or ENT_HTML5), but only when ENT_QUOTES is set
< (less than) &lt;
>
(greater than) &gt;
See this page as a reference here

I have tried to encode/decode the string with StringBuilder but the string was fully readable in mySQL. I used UTF8 General ci (Domain at one.com)

This example concerns the code in Code Area, here's the code for sending my data


B4X:
private Sub SendDataToServer
    
    ' Encode Code Area for Saving to Server in mySQL DB
    'EncodeCodeArea
    
    ' Get current date and time
    Dim CurrentDateTime As String
    DateTime.DateFormat = "yyyy-MM-dd HH:mm:ss"
    CurrentDateTime = DateTime.Date(DateTime.Now)

    ' Initialize Sending of data from Documents manager
    SendJob1.Initialize("Job1", Me)
    'Send a POST request
    SendJob1.PostString("https://www. mydomain and script... .php", _
    "data1=" & ListviewCategories.SelectedItem & _
    "&data2=" & TableView.SelectedRowValues(2) & _
    "&data3=" & Username & _
    "&data4=" & HTMLEditor1.HtmlText & _
    "&data5=" & CodeArea1.GetText.Replace("'", "<apos>") & _
    "&data6=" & CurrentDateTime & _
    "&data7=" & "012345")

End Sub
 

ThRuST

Well-Known Member
Licensed User
I use PHP to build a clone of mySQL contents into a SQLite database file on a server in Denmark (one.com) and once buildt I download the SQLite file as a zip and extract it locally to display in a tableview. It's all an automated process. SQLite3 easily causes errors with PHP if the special chars is not properly decoded.
 

ThRuST

Well-Known Member
Licensed User
We all need a proper way to handle this encoding/decoding process. Prefferably a much more enhanced version of this

B4X:
' Remove "Illegal" characters
Sub FixString(S As String) As String
  
    ' Single Quote replaced with chr(31)
    ' Double Quote replaced with chr(30)
  
    S=S.Replace("'",Chr(31))
    S=S.Replace(Chr(34),Chr(30))
  
    Return S
  
End Sub

' Restore "Illegal" characters
Sub RestoreString(S As String) As String

    ' Chr(31) replaced with Single Quote
    ' Chr(30) replaces with Double Quote, Chr(34)
  
  
    S=S.Replace(Chr(31), "'")
    S=S.Replace(Chr(30),Chr(34))

    Return S

End Sub
 

ThRuST

Well-Known Member
Licensed User
@Erel Btw I got the idea to use "< >" so I used <apos> based from one of your old posts describing HTML encoding. After a whole days search I finnaly understood that your example was the best to follow :)
 
Top