Android中Kotlin连接Web托管SQL Server失败问题排查

我拥有一个Web托管服务并在此创建了SQL Server数据库，使用以下C#连接字符串可正常连接：

<add name="NetContext" connectionString="Data Source=sql.atlas.domainhizmetleri.com,1433;Database=DbDeneme;User Id=denemeuser;Password=123456;" providerName="System.Data.SqlClient" />

但在Android Studio中使用Kotlin代码尝试建立连接时始终失败，以下是我的Kotlin代码：

private val ip = "sql.atlas.domainhizmetleri.com"
private val db = "DbDeneme"
private val un = "denemeuser"
private val pass = "123456"
private val port = "1433"

override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    binding = ActivityMainBinding.inflate(layoutInflater)
    setContentView(binding.root)

    val fade_in = AnimationUtils.loadAnimation(this, R.anim.fade_in)
    val bottom_down = AnimationUtils.loadAnimation(this, R.anim.bottom_down)

    btngiris.setOnClickListener {
        val kullaniciAdi = txtkullanici.text.toString()
        val sifre = txtsifre.text.toString()

        if (kullaniciAdi.trim().isEmpty() || sifre.trim().isEmpty()) {
            Toast.makeText(applicationContext, "Kullanıcı adı ve şifrenizi giriniz", Toast.LENGTH_LONG).show()
        } else {
            try {
                val con = connectionclass(ip, port, db, un, pass)
                val ConnectionURL = "jdbc:jtds:sqlserver://$ip:$port;databasename=$db;User=$un;password=$pass;"

                if (con == null) {
                    Toast.makeText(applicationContext, "İnternet bağlantınızı kontrol ediniz", Toast.LENGTH_LONG).show()
                } else {
                    val query = "SELECT * FROM Personeller WHERE TC='$kullaniciAdi' AND Sifre='$sifre'"
                    val stmt: Statement = con.createStatement()
                    val rs: ResultSet = stmt.executeQuery(query)

                    if (rs.next())
                    {
                        val intent = Intent(this@MainActivity, Dashboard::class.java)
                        val PersonelAdi = rs.getString(3)
                        val PersonelSoyAdi = rs.getString(4)
                        val PersonelTc = rs.getString(7)
                        intent.putExtra("PersonelAdi", PersonelAdi)
                        intent.putExtra("PersonelSoyAdi", PersonelSoyAdi)
                        intent.putExtra("PersonelTc", PersonelTc)
                        startActivity(intent)
                    } else {
                        Toast.makeText(applicationContext, "Geçersiz Kimlik Bilgileri", Toast.LENGTH_LONG).show()
                    }
                    con.close()
                }
            } catch (ex: Exception) {
                ex.message?.let { it1 -> Log.e("Error", it1) }
                Toast.makeText(applicationContext, "Bir hata oluştu", Toast.LENGTH_LONG).show()
            }
        }
    }
}

@SuppressLint("NewApi")
fun connectionclass(ip: String, port: String, db: String, un: String, pass: String): Connection? {
    StrictMode.setThreadPolicy(StrictMode.ThreadPolicy.Builder().permitAll().build())
    var con: Connection? = null
    val ConnectionURL = "jdbc:jtds:sqlserver://$ip:$port;databasename=$db;User=$un;password=$pass;"
    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver")
        con = DriverManager.getConnection(ConnectionURL)
    } catch (exception: Exception) {
        exception.message?.let { Log.e("Error", it) }
    }
    return con
}

问题排查与修复方案 #

1. 主线程执行耗时操作 #

Android 禁止在主线程执行网络/数据库这类耗时操作，即使通过StrictMode.permitAll()绕过检查，也会导致连接不稳定、ANR（应用无响应）或直接失败。必须将数据库操作移到后台线程，推荐使用Kotlin协程。

2. SQL注入风险与语法错误 #

直接拼接SQL字符串不仅存在严重的SQL注入漏洞，还可能因用户输入包含特殊字符（如单引号）导致SQL语法错误，必须使用PreparedStatement预编译查询。

3. JTDS驱动连接字符串配置问题 #

托管的SQL Server通常要求SSL加密连接，C#的System.Data.SqlClient默认启用SSL，但JTDS驱动需要手动指定SSL参数。另外，连接字符串参数名需匹配JTDS规范：

• 添加ssl=require强制SSL连接
• 统一参数大小写（JTDS对参数名大小写敏感，如user和password需小写）

4. 依赖与权限缺失 #

• 确保在app/build.gradle中添加JTDS驱动依赖：

  dependencies {
      implementation 'net.sourceforge.jtds:jtds:1.3.1'
  }
  

• 确保AndroidManifest.xml添加网络权限：

  <uses-permission android:name="android.permission.INTERNET" />
  

5. 冗余代码问题 #

onClick方法中重复定义的ConnectionURL未被使用，属于冗余代码，可直接删除。

修复后的完整代码示例 #

private val ip = "sql.atlas.domainhizmetleri.com"
private val db = "DbDeneme"
private val un = "denemeuser"
private val pass = "123456"
private val port = "1433"

override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    binding = ActivityMainBinding.inflate(layoutInflater)
    setContentView(binding.root)

    val fade_in = AnimationUtils.loadAnimation(this, R.anim.fade_in)
    val bottom_down = AnimationUtils.loadAnimation(this, R.anim.bottom_down)

    btngiris.setOnClickListener {
        val kullaniciAdi = txtkullanici.text.toString().trim()
        val sifre = txtsifre.text.toString().trim()

        if (kullaniciAdi.isEmpty() || sifre.isEmpty()) {
            Toast.makeText(applicationContext, "Kullanıcı adı ve şifrenizi giriniz", Toast.LENGTH_LONG).show()
        } else {
            // 协程后台执行数据库操作
            CoroutineScope(Dispatchers.IO).launch {
                val con = getConnection()
                var loginSuccess = false
                var personelAdi = ""
                var personelSoyAdi = ""
                var personelTc = ""

                con?.use { connection ->
                    // 预编译查询避免注入
                    val query = "SELECT * FROM Personeller WHERE TC=? AND Sifre=?"
                    connection.prepareStatement(query).use { stmt ->
                        stmt.setString(1, kullaniciAdi)
                        stmt.setString(2, sifre)
                        val rs = stmt.executeQuery()

                        if (rs.next()) {
                            loginSuccess = true
                            personelAdi = rs.getString(3)
                            personelSoyAdi = rs.getString(4)
                            personelTc = rs.getString(7)
                        }
                    }
                }

                // 切回主线程更新UI
                withContext(Dispatchers.Main) {
                    if (loginSuccess) {
                        val intent = Intent(this@MainActivity, Dashboard::class.java)
                        intent.putExtra("PersonelAdi", personelAdi)
                        intent.putExtra("PersonelSoyAdi", personelSoyAdi)
                        intent.putExtra("PersonelTc", personelTc)
                        startActivity(intent)
                    } else {
                        Toast.makeText(applicationContext, "Geçersiz Kimlik Bilgileri", Toast.LENGTH_LONG).show()
                    }
                }
            }
        }
    }
}

private fun getConnection(): Connection? {
    var con: Connection? = null
    // 修正连接字符串，添加SSL强制参数
    val connectionUrl = "jdbc:jtds:sqlserver://$ip:$port;databaseName=$db;user=$un;password=$pass;ssl=require;"
    try {
        Class.forName("net.sourceforge.jtds.jdbc.Driver")
        con = DriverManager.getConnection(connectionUrl)
    } catch (exception: Exception) {
        Log.e("Error", "连接失败: ${exception.message}")
    }
    return con
}

额外建议 #

• 不要直接在客户端暴露数据库账号密码，这会导致严重的安全风险。正确的做法是搭建中间API接口，客户端通过API与数据库交互，避免直接连接数据库。
• 处理数据库连接时使用use函数，自动关闭资源，避免内存泄漏。

内容的提问来源于stack exchange，提问作者Burhan Kes.