Ajax character encoding, weird going into SQL

Question

I have 2 asp classic pages, both of which do the same thing. Take strings and put them in a DB.

Test1.asp:

<%@Language=VBScript %>
<%OPTION EXPLICIT%>


<!-- #INCLUDE FILE="../G_FILES/globals.asp"-->
<!-- #INCLUDE FILE="../g_files/validation.asp"-->
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title></title>
<link rel="stylesheet" type="text/css" href="<%=gStyle%>">
<script language="javascript" src="../js_files/jquery/jquery-1.10.2.min.js"></script>
<script src="../js_files/jquery/jquery.limit-1.2.source.js"></script>
<script type="text/javascript" src="../js_files/webdb_helper.js"></script>
<script language="javascript">


    $(document).ready(function () {
        //CHARACTER COUNT/LIMITATION TEXTAREA
        $('#body').limit('3000', '#charLeft');
    });




    //Save Message
    $(document).ready(function() {
        $("#saveBtn").click(function() {

            var subject = $('#subject').val();
            var body = $('#body').val();


            var all_good = true;
            //NO ERRORS? INSERT INTO DB


            if (all_good) {
                $.ajax({
                    type: 'POST',
                    url: "edit_template_test.asp",
                    data: {
                        subject: subject,
                        body: body
                    },
                    async: false,
                    cache: false,
                    error: function(jqXHR, textStatus) {
                        alert("Request failed: " + textStatus);
                        var error = jqXHR.responseText;
                        alert(error);
                    }
                }).done(function(response) {
                    //reload message list
                    alert(response);
                });
            }

        });
    });

</script>
</head>

<body style ="width:700px">
<center class="BodyTextBoldDoubleTitle">Service Provider Communications</center><br />

<table class="BodyTextBoldSingle">

    <tr>
        <td>Email Subject:</td>
        <td colspan="4"><input type="text" size="91"  maxlength='120' id="subject" value =""/></td>
    </tr>   

    <tr><td colspan="5">&nbsp;</td></tr>
    <tr>
        <td colspan="5">Body:</td>
    </tr>
    <tr>
        <td colspan="5"><textarea id='body' rows="13" cols="80"></textarea></td>
    </tr>
    <tr>
        <td colspan="4">You have <font id="charLeft">0</font>/3000 characters left.</td>

    </tr>

    <table class="BodyTextBoldSingle">
        <tr>

            <td>&nbsp;&nbsp;&nbsp;</td>
            <td><div id="saveBtn" style="border: 2px solid; border-radius: 40px 40px; background-color: #C0C0C0; height: 25px; width:155px; text-align: center; vertical-align: middle; line-height: 25px; cursor: pointer" ><font class="BodyTextBoldSingle">Save</font></div></td>
        </tr>
    </table>
</center>
</body>
</html>

this calls edit_template_test.asp via ajax:

<%@Language=VBScript %>
<%OPTION EXPLICIT%>
<!-- #include file="../g_files/globals.asp" -->
<!-- #INCLUDE FILE="../g_files/validation.asp"-->

<%

    dim subject,body

    subject = Request.Form("subject")
    body = Request.Form("body")


        dim db, sql
        set db = Server.CreateObject("Commands.DB")
        sql = "update folder_log set subject = ' " & subject & "', note = '" & body & "' where msg_id       = '00D8EE44-197D-4F95-938E-887005FCE0D6'"
        db.RunCMD g_Conn, sql
        Response.Write subject&"^"&body


    %>

Test2.asp:

<%@Language=VBScript %>
<%OPTION EXPLICIT%>

<!-- #INCLUDE FILE="../G_FILES/globals.asp"-->
<!-- #INCLUDE FILE="../g_files/validation.asp"-->
<%

dim body1, subject1
Dim test_send
    test_send= trim(request.Form("test_send"))
if test_send = "yes" then
    Response.Write "hello"
    body1 = request.Form("body2")
    subject1 = request.Form("subject2")

    dim db, sql
    set db = Server.CreateObject("Commands.DB")
    sql = "update folder_log set subject = ' " & subject1 & "', note = '" & body1 & "' where msg_id = '7E59E4EE-6682-4C74-BD65-9F0244742BC5'"
    db.RunCMD g_Conn, sql


end if

 %>
<html>
<head>

<title></title>
<link rel="stylesheet" type="text/css" href="<%=gStyle%>">
<script language="javascript" src="../js_files/jquery/jquery-1.10.2.min.js"></script>
<script src="../js_files/jquery/jquery.limit-1.2.source.js"></script>
<script type="text/javascript" src="../js_files/webdb_helper.js"></script>
<script language="javascript">


</script>
</head>

<body style ="width:700px">
<form name="test2" id="test2" method="post" action="test2.asp" >
<center class="BodyTextBoldDoubleTitle">Service Provider Communications</center><br />

<table class="BodyTextBoldSingle">

    <tr>
        <td>Email Subject:</td>
        <td colspan="4"><input type="text" size="91" name="subject2" maxlength='120' id="subject2" value =""/></td>
    </tr>   

    <tr><td colspan="5">&nbsp;</td></tr>
    <tr>
        <td colspan="5">Body:</td>
    </tr>
    <tr>
        <td colspan="5"><textarea name="body2" id="body2" rows="13" cols="80"></textarea></td>
    </tr>
    <tr>
        <td colspan="4">You have <font id="charLeft">0</font>/3000 characters left.</td>

    </tr>

       <tr>

            <td>&nbsp;&nbsp;&nbsp;</td>
            <input type="hidden" name="test_send" value="yes" ID="test_send">
            <td><input type="submit" name = "Save"  /></td>
        </tr>
    </table>
</center>
</form>
</body>
</html>

Both Test1 and Test2 produce the same desired outcomes (entry into DB) except for one thing.

When a string is copied and pasted from Word that contains one of Words different characters (TEST – “TEST” for example), Test2.asp (post back) works perfectly, but Test1.asp will put funky characters into the DB (TEST – “TEST” -> TEST â€“ â€œTESTâ€).

Has anyone any idea why the post back would work fine but the ajax route has encoding problems?

I have tried all sorts of solutions to get it to work. ie:

contentType: 'Content-type: text/plain; charset=iso-8859-1',
                    beforeSend: function(jqXHR) {
                        jqXHR.overrideMimeType('text/html;charset=iso-8859-1');
                    },

And other types of methods of encoding/decoding. I really want to get the ajax method working.

Anyone got an explanation?

If it is relevant the char encoding within the sql server is SQL_Latin1_General_CP1_CI_AS.

Using all capitals in the title is considered SHOUTING. Try to avoid that. — Afsa, Sep 19 '14 at 19:10
Post something on SO for 30 seconds and SOMEONE will mess with it. ;) — crthompson, Sep 19 '14 at 19:13
This doesn't answer your question, but suppose someone entered a subject of "my subject', msg_id = '0". That would cause a big integrity problem and much worse could be done. You should always use parameterized queries. — Chris Barlow, Sep 26 '14 at 04:10
the query is just a quick example. The issue is encoding problems and how using post back method works and ajax doesn't. — Mildfire, Sep 26 '14 at 20:04
I guess the html fields and textareas contents are encoded using the HTML page encoding, so if the page defaults to UTF-8 you will get UTF-8 text sent to a Latin 1 database. One way to try if this is the case is to set manually (in the browser) the page encoding to Latin 1 before clicking the button to send the ajax request. — FrancescoMM, Oct 01 '14 at 13:20

score 1 · Answer 1 · edited May 23 '17 at 12:05

This is due to character encoding. Ensure that you've saved all your .ASP files with UTF-8 encoding (open them in e.g. Notepad, select File > Save As and select UTF-8 encoding in the Save As dialog).

Also you should change your code in "edit_template_test.asp" to get rid of any nasty SQL-injects (imagine if you was sent '; DROP TABLE ... -- in the subject parameter). Do something like this:

dim subject,body

subject = Request.Form("subject")
body = Request.Form("body")

dim db, sql
set db = Server.CreateObject("Commands.DB")
sql = "update folder_log set subject = ' " & Replace(subject,"'","''") & "', note = '" & Replace(body,"'","''") & "' where msg_id       = '00D8EE44-197D-4F95-938E-887005FCE0D6'"
db.RunCMD g_Conn, sql
Response.Write Server.HtmlEncode(subject&"^"&body)

Anyway, saving the files in UTF-8 encoding should switch all browsers to render and post data in UTF-8. If not, you might need to do something like this for ASP classic: Classic ASP's Request.Form is dropping an 8-bit character -- is there a simple way to prevent this?

Best of luck!

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

Try removing the DOCTYPE declaration from Test1.asp

Also SQL_Latin1_General_CP1_CI_AS is just a collation property for SQL server (default:ASCII)

edited May 23 '17 at 12:21

Community

1
1

answered Sep 29 '14 at 18:22

Andy Merhaut

305
1
6

score 0 · Answer 3 · answered Oct 01 '14 at 12:28

javascript and thus ajax standard encoding for strings is always utf8!
so before passing your strings to your ajax functions, be sure to convert them to utf8.
and when receiving them from ajax, be aware that you received utf8 encoded strings and then convert them into any encoding you want.

Jean · Answer 4 · 2014-10-01T14:23:52.840

0

Try to add

<meta charset="UTF-8">

on the head of your html file

Why UTF-8:

UTF-8 supports more characters than ASCII, allowing international languages to be represented.

edited Oct 01 '14 at 14:23

answered Oct 01 '14 at 13:58

Jean

5,201
11
51
87

Ajax character encoding, weird going into SQL

4 Answers4