例子: SQL 注入

现在有关系 Accounts(name,passwd,acct).

Web 界面: 获取用户的用户名和密码, 将它们分别存储在字符串变量 n 和 p 中, 然后执行查询, 显示 account number.

SELECT acct FROM Accounts
WHERE name = n AND passwd = p

这里不同的语言有不同的处理方式.

实验

在 test_students 数据库中创建表 Accounts(name, passwd, acct).

CREATE TABLE Accounts(
  name VARCHAR(50),
  passwd VARCHAR(50),
  acct DECIMAL(20,2)
);

插入一些数据.

INSERT INTO Accounts VALUE ("gates", "SecretString", 10000000000.10);

mysql> select * from Accounts;
+-------+--------------+----------------+
| name  | passwd       | acct           |
+-------+--------------+----------------+
| gates | SecretString | 10000000000.10 |
+-------+--------------+----------------+
1 row in set (0.00 sec)

用户输入

-- 也是 MySQL 中的注释符.

查询真正执行的是什么

SELECT acct FROM Accounts WHERE name = 'gates' -- ' AND    passwd = 'who cares?';

-- 将后面的 AND passwd = 'who cares?'; 都当作注释语句. 注意 -- 后面需要加空格.

mysql> SELECT acct FROM Accounts WHERE name = 'gates' -- ' AND    passwd = 'who cares?';
    -> ;
+----------------+
| acct           |
+----------------+
| 10000000000.10 |
+----------------+
1 row in set (0.00 sec)

宿主语言与 SQL 通过库的接口(Host/SQL Interfaces Via Libraries)

宿主语言与 SQL 通过库的接口

对于常规语言连接数据库的第三种方式是利用库的调用.

C + CLI
Java + JDBC
PHP + PEAR/DB

三层体系结构(Three-Tier Architecture)

三层体系结构

一般大型数据库的通用环境有下面三层体系结构.

Web servers: 管理与用户的交互.
Application servers: 执行“交易逻辑”(business logic)
Database servers: 执行应用服务器请求的从数据库中查询或更新.

例子: Amazon

数据库中存储物品、顾客等的信息.

交易逻辑中包含了诸如“当某人点击了 'checkout' 后我该做什么”之类的事情.

Answer: 显示“如何支付”的页面.

环境, 连接, 查询(Environments, Connections, Queries)

环境, 连接, 查询

在许多 DB-access 语言中, 数据库就是一个环境(enviroment).

数据库服务器维持一定数量的连接(connection), 从而应用服务器可以执行查询或更新.

应用服务器发布语句(statements): 通常是查询和更新.

需要记住的一幅图

SQL/CLI

这里我们并不象(如嵌套 SQL)那样利用预处理器, 而是使用函数库.

C 的函数库称为 SQL/CLI, 其中 CLI 是 Call-Level Interface (调用级接口)的缩写.
嵌套 SQL 的预处理器将把以 EXEC SQL 开头的语句翻译成 CLI 或其他类似的函数调用.

数据结构(Data Structures)

C 通过下面四种类型的结构(struct)来连接数据库:

环境记录(environment): 由应用(客户)程序创建, 为与数据库服务器的一个或多个连接做准备.
连接记录(connection): 登录数据库. 即用于连接应用程序和数据库. 每个连接记录存在于某个环境记录中.
语句记录(statement): 传递给连接的 SQL 语句. 应用程序可以创建一个或多个语句记录. 每个语句记录保存了单条 SQL 语句的信息, 如果时查询语句则还包括隐含的游标. 不同时刻, 同一个 CLI 语句代表不同的 SQL 语句. 每条 CLI 语句存在于某一连接记录中.
描述记录(description): 保存某查询的元组信息或是某语句的参数信息. 在 CLI 的表示中, 描述记录一般都是不可见的.

句柄(Handles)

句柄是记录的指针. 头文件 sqlcli.h 分别提供了环境记录、连接记录、语句记录和描述记录的句柄类型. 分别是: SQLHENV, SQLHDBC, SQLHSTMT, SQLHDESC. 它们都被看作是指针或整型.

函数 SQLALLocHandle(T,I,O) 用于创建这三种结构(SQLHENV, SQLHDBC, SQLHSTMT).

T=type, 如: SQL_HANDLE_STMT.
I=input handle=下一个更高级别的结构(即更高层元素的句柄) (statement < connection < environment).
O=是由 SQLALLocHandle 所创建的句柄地址.

例子: SQLALLocHandle

SQLALLocHandle(SQL_HANDLE_STMT, myCon, &myStat);

myCon 是之前创建的连接句柄.
myStat 是将要创建的语句句柄的名称.
函数 SQLALLocHandle 返回一个 SQLRETURN (整数)类型的值. 当值是 0 时表示没有错误发生, 如果值非零, 则意味着发生了错误.

例子: 声明和创建环境、连接和语句记录

#include "sqlcli.h"
SQLHENV myEnv; /* 创建环境句柄 */
SQLHDBC myCon; /* 创建连接句柄 */
SQLHSTMT execStat; /* 创建语句句柄 */
SQLRETURN errorCode1, errorCode2, errorCode3;

errorCode1=SQLALLocHandle(SQL_HANDLE_ENV,
  SQL_NULL_HANDLE, &myEnv);
if(!errorCode1){
  errorCode2=SQLALLocHandle(SQL_HANDLE_DBC,
    myEnv, &myCon);
  if(!errorCode2){
    errorCode3=SQLALLocHandle(SQL_HANDLE_STMT,
      myCon, &execStat);
  }
}

准备和执行(Preparing and Executing)

上面最后创建了句柄为 execStat 的语句记录. 但还没有与该记录相关联的 SQL 语句.

与语句句柄相关联且执行 SQL 语句的进程与动态 SQL 很相似. 都是先准备(prepare)再执行(execute).

SQLPrepare(H,S,L) 将长度为 L 的字符串解释为一个 SQL 语句. 并使得语句句柄 H 现在保存该可执行语句.
SQLExecute(H) 令被语句句柄 H 所代表的 SQL 语句执行.

例子: 准备和执行(Prepare and Execute)

SQLPrepare(execStat, 
    "SELECT beer, price FROM Sells
     WHERE bar='Joe''s Bar'",
    SQL_NTS);
SQLExecute(execStat);

SQL_NTS 这个常量将通知 SQLPrepare 其第二个参数是一个以空值结束的字符串(null-terminated string), 让其确认字符串的长度, 即只能从字符串本身来计算长度.

直接执行(Direct Execution)

如果对于语句 S 我们只执行一次, 则可以将上面的两个步骤(Prepare 和 Execute) 合在一起.

SQLExecDirect(H,S,L);

与前面一样, H 是代表语句 S 的句柄, L 是字符串 S 的长度.

请具体查询 sqlcli.h 文件

取出元组数据(Fetching Tuples)

当执行的 SQL 语句是一个查询, 我们需要从结果关系中取出这些元组.

由于我们执行的是一个查询, 因此需要一个游标. 故游标必须在此前进行声明.

SQLFetch(H) 从句柄 H 所代表的 SQL 语句的结果关系中取出下一个元组.

Accessing Query Results

当我们取出一个元组后, 我们需要将该元组的组成部分(各个属性值)用到其他地方去.

每个组成部分通过函数 SQLBindCol 被绑定到一个变量.

SQLBindCol 这个函数有 6 个参数
SQLBindCol(sh,colNo,colType,pVar,varSize,varInfo)

sh: 是所涉及的语句的句柄.
colNo: 列号, 即分量值在所获得元组中的位置, 是第几个分量.
colType: 是一个代码, 表示存放的分量值的变量类型. 在 sqlcli.h 中, SQL_CHAR 表示字符数组和字符串, SQL_INTEGER 表示整型.
pVar: 是一个指针, 指向存放值的变量.
varSize: 是 pVar 指向变量值的字节长度.
varInfo: 是一个整型指针, SQLBindCol 用其提供关于输出值的附加信息.

我们只需用到第 1，2 和 4 个参数.

例子: 绑定(Binding)

假设我们刚执行了 SQLExecute(execStat), 其中 execStat 是代表下面查询语句的句柄:

SELECT beer, price FROM Sells
WHERE bar='Joe''s Bar'

将所得元组的两个分量值绑定到变量 theBeer, thePrice.

SQLBindCol(execStat,1,,&theBeer,,);
SQLBindCol(execStat,2,,&thePrice,,);

例子: 取出所有元组(Fetching Tuples)

现在, 我们利用循环可以取出所有的元组.

while(SQLFetch(execStat)!=SQL_NO_DATA)
{
    /* do something with theBeer and thePrice */
}

SQL_NO_DATA 是一个 CLI 宏, 代表 SQLSTATE='02000', 即结果中没有元组了.

MySQL C API

在数据库中新建一个名为 test_db3 的数据库.

#include <my_global.h>
#include <mysql.h>

int main(int argc, char **argv)
{

	MYSQL *conn;

	conn = mysql_init(NULL);

	if (conn == NULL) {
		printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
		exit(1);
	}

	if (mysql_real_connect(conn, "localhost", "haifeng", "34klq*", NULL, 0, NULL, 0) == NULL) {
		printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
		exit(1);
	}

	if (mysql_query(conn, "create database test_db3")) {
		printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
		exit(1);
	}

	mysql_close(conn);

}

使用 gcc 编译

gcc version.c -o version  `mysql_config --cflags --libs`

如果出现下面的提示:

In file included from /usr/include/pthread.h:21:0,
                 from /usr/include/mysql/my_global.h:274,
                 from connect.c:1:
/usr/include/features.h:330:4: 警告：#warning _FORTIFY_SOURCE requires compiling with optimization (-O) [-Wcpp]

则在刚才的编译命令中加一个 -O 参数, 即

gcc version.c -o version  `mysql_config --cflags --libs` -O

实验

实验(MySQL)

连接数据库 test_students, 打印 studentinfo 表中的内容.

将下面的文件保存为 main.cpp. 使用 CodeBlocks 新建一个工程, 比如 mysql-connect.

/**
* 连接MySQL, 注意如果连接的是远程服务器, 注意密码保护.
* 假设本地安装的是 WampServer, 编译时需要包含其中的头文件以及链接到相应的库文件.
*/
#include <stdio.h>
#include <stdlib.h>
//#include <winsock2.h>

#include "C:/wamp64/bin/mysql/mysql5.7.23/include/mysql.h"

void finish_with_error(MYSQL *con)
{
    fprintf(stderr, "%s\n", mysql_error(con));
    printf("Error %u: %s\n", mysql_errno(con), mysql_error(con));
    mysql_close(con);
    exit(1);
}

int main(int argc, char **argv)
{
    //注意：这里打印的版本与所使用的库有关. 如果本地开启的MySQL是5.6.17,仍会显示库所用的版本5.5.27
    printf("MySQL local client version: %s\n", mysql_get_client_info());

    MYSQL *conn;
    conn = mysql_init(NULL);

    if (conn == NULL) {
        //fprintf(stderr, "mysql_init() failed\n");
        printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
        exit(1);
    }

    //连接远程服务器需要用的参数
    const char* HOST="远程服务器的IP地址";//或者连接"localhost"作测试
    const char* User="数据库用户名";
    const char* Password="该用户的数据库密码";
    const char* Database="test_students";//待连接的数据库名称
    unsigned int Port=3306;

    if (mysql_real_connect(conn, HOST, User, Password, Database, Port, NULL, 0) == NULL)
    {
        finish_with_error(conn);
    }
    printf("connect to Host: %s sucessfully\n", HOST);
    printf("MySQL remote client version: %s\n", mysql_get_client_info());

    mysql_query(conn, "SET NAMES GBK;");
    if (mysql_query(conn, "select * from studentinfo;")) {
        printf("Error %u: %s\n", mysql_errno(conn), mysql_error(conn));
        finish_with_error(conn);
    }

    printf("MySQL client version: %s\n", mysql_get_client_info());

    MYSQL_RES *result = mysql_store_result(conn);

    if (result == NULL)
    {
        finish_with_error(conn);
    }

    int num_fields = mysql_num_fields(result);
    printf("num of fields: %d\n", num_fields);

    MYSQL_ROW row;

    while ((row = mysql_fetch_row(result)))
    {
        for(int i = 0; i < num_fields; i++)
        {
            printf("%s ... ", row[i] ? row[i] : "NULL");
        }
        printf("\n");
    }

    printf("------\n");

    mysql_free_result(result);
    mysql_close(conn);
    return 0;
}

编译

Debug

g++.exe -Wall -fexceptions -g -IC:\wamp64\bin\mysql\mysql5.7.23\include -c D:\work\cs\c++\database\mysql\mysql-c-connect\main.cpp -o obj\Debug\cs\c++\database\mysql\mysql-c-connect\main.o

g++.exe  -o bin\Debug\mysql-connect.exe obj\Debug\cs\c++\database\mysql\mysql-c-connect\main.o   C:\wamp64\bin\mysql\mysql5.7.23\lib\libmysql.lib

Release

g++.exe -Wall -fexceptions -O2 -I"C:\Program Files (x86)\MySQL\MySQL Server 5.5\include" -c D:\work\cs\c++\database\mysql\mysql-c-connect\main.cpp -o obj\Release\cs\c++\database\mysql\mysql-c-connect\main.o

g++.exe  -o bin\Release\mysql-connect.exe obj\Release\cs\c++\database\mysql\mysql-c-connect\main.o  -s  C:\wamp64\bin\mysql\mysql5.7.23\lib\libmysql.lib

JDBC

Java Database Connectivity(JDBC) 是类似于 SQL/CLI 的一个函数库, 但宿主语言是 Java.

类似于 CLI, 我们所要用到的, 两者之间有少许区别.

建立连接

import java.sql.*;
Class.forName(com.mysql.jdbc.Driver);
Connection myCon =
  DriverManager.getConnection(
    <URL>, <username>, <password>
  );

java.sql.* 是 JDBC 的类.
com.mysql.jdbc.Driver 是关于 MySQL 的驱动.
DriverManager: Loaded by forName
getConnection(...) 中的参数是数据库的 URL, 用户名, 密码等.

Statements

JDBC 提供了两个类:

Statement: 可以接受一个 SQL 语句字符串, 并可以执行这条语句.
PreparedStatement: 具有相应的 SQL 语句并准备执行的对象.

Creating Statements

这个连接类(Connection class) 有建立语句方法(createStatement()) 和准备语句方法(PreparedStatement()).

Statement stat1 = myCon.createStatement();
PreparedStatement stat2 =
  myCon.createStatement(
    "SELECT beer, price FROM Sells " +
    "WHERE bar = 'Joe''s Bar' "
);

没有参数的 createStatement 返回一个 Statement;
如果含有一个参数, 则返回 PreparedStatement.

执行 SQL 语句

JDBC 根据具体更改(也称为“更新”)来将查询分类.

Statement 和 PreparedStatement 分别有方法 executeQuery 和 executeUpdate.

对于 Statement: 一个参数, 查询或修改被执行.
对于 PreparedStatement: 没有参数.

例子: Update

之前定义的 Stat1 是一 Statement. 我们可以利用它来加入一条记录(元组).

stat1.executeUpdate(
  "INSERT INTO Sells " +
  "VALUES('Brass Rail','Bud',3.00)"
);

例子: Query

stat2 是一含有查询语句 "SELECT beer,price FROM Sells WHERE bar='Joe''s Bar'" 的 PreparedStatement.

executeQuery 返回 ResultSet 类型的对象. 它是由该查询语句产生的元组的包.

ResultSet menu = stat2.executeQuery();

访问 ResultSet

ResultSet 类型的对象有点像一个游标(cursor).

方法 next() 将“游标”指向下一个元组.

第一次应用 next() 将得到第一个元组.
如果没有元组了, 则 next() 返回 false.

访问元组的组成部分

当 ResultSet (作为游标) 指向一个元组时, 我们可以应用某些方法到 ResultSet 上以获取其各个组成部分的值.

方法 getX(i), 这里 X 是某个类型, 如getString(i), getInt(i),getFloat(i). 其中 i 是指第几个组成部分. 返回的是该组成部分的值.

这个返回值必须是 X 类型的.

例子: 访问元组的组成部分

ResultSet menu = stat2.executeQuery();

即 menu 是之前由查询语句 SELECT beer, price FROM Sells WHERE bar = 'Joe''s Bar' 产生的元组包. 是 ResultSet 类型的.

使用下面的循环从每一个元组中得到 beer 和 price 的值.

while ( menu.next() ) {
  theBeer = menu.getString(1);
  thePrice = menu.getFloat(2);
  /*something with theBeer and thePrice*/
}

Java 连接 MySQL 数据库

References:
http://zetcode.com/db/mysqljava/

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package helloworld;

import java.util.Date;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
 *
 * @author haife
 */
public class HelloWorld {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO code application logic here
        System.out.println("Hello Java!\n The Date and Time is: ");
        System.out.println(new Date());

        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        String url = "jdbc:mysql://localhost:3306/test_bar";
        String user = "root";
        String password = "34klq*";

        try {
            
            con = DriverManager.getConnection(url, user, password);
            st = con.createStatement();
            rs = st.executeQuery("SELECT * FROM Bars");

                System.out.println("name\t addr\t license\n");
            while(rs.next()) {
                
                System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));
            }

        } catch (SQLException ex) {
        
            Logger lgr = Logger.getLogger(HelloWorld.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);

        } finally {
            
            try {
                
                if (rs != null) {
                    rs.close();
                }
                
                if (st != null) {
                    st.close();
                }
                
                if (con != null) {
                    con.close();
                }

            } catch (SQLException ex) {
                
                Logger lgr = Logger.getLogger(HelloWorld.class.getName());
                lgr.log(Level.WARNING, ex.getMessage(), ex);
            }
        }
    }
}

Java 连接 PostgreSQL 数据库

package ExamplePackage;

import java.sql.*; 
import java.util.*; 

public class ConnectionManager { 
	static Connection con; 
	static String url; 
	
	public static Connection getConnection() 
	{ 
		try 
		{
			//String ipAddr="127.0.0.1";
			//String port="5432";
			//String dbname="test_db";
			//String url = "jdbc:postgresql://"+ipAddr+":"+ port + "/"+dbname; 
		    String url = "jdbc:postgresql://127.0.0.1:5432/test_db";
			
			// assuming "DataSource" is your DataSource name 
			
			Class.forName("org.postgresql.Driver"); 
			
			try 
			{  
				con = DriverManager.getConnection(url,"postgres","");
				
				// assuming your SQL Server's username is "username" 
				// and password is "password" 
			} 
			catch (SQLException ex) 
			{ 
				ex.printStackTrace(); 
			} 
		} 
		catch(ClassNotFoundException e) 
		{ 
			System.out.println(e); 
		} 
		return con; 
		} 
	}

PHP

PHP 是一种可以嵌入 HTML 文本中的语言.
PHP 代码放在 <?PHP ... ?> 中.
DB library 位于 PEAR 中. (PHP Extension and Application Repository)
- 用语句 include(DB.php) 包含.

PHP 中的变量

必须以 $ 开始.
不必先声明变量的类型.
当对属于某个类的变量赋值时, 这个类的方法(函数)必须可以应用它.

字符串

PHP 解决了很多语言都碰到的非常重要的问题, 即.

如何告知某个子字符串是被解释为一个变量还是用该变量的值来替代它?

PHP 的解决办法是: 双引号括起来的变量在引用时用它的值替代, 单引号则忠实地表示字符串.

例子: Replace or Not?

$100 = "one hundred dollars";
$sue = 'You owe me $100.';
$joe = "You owe me $100.";

$sue 的值是 You owe me $100., 而 $joe 的值为 You owe me one hundred dollars

PHP 数组

PHP 有两种数组: 普通数组(数字型数组,numeric), 关联数组(associative).

数字型数组用下标 0,1,2,... 表示.

例如: $a = array("Paul","George","John","Ringo");
则 $a[0] 的值是 Paul, $a[1] 的值是 George, 等等.

关联数组(Associative Arrays)

关联数组 $a 的元素是形如 x=>y 这样的配对, 其中 x 是键(key string), 而 y 是它的值.

若 x=>y 是 $a 的元素, 则 $a[x] 就等于 y.

例子: 关联数组

一个环境可以表示成一个关联数组, 例如:

$myEnv = array (
  "phptype" => "oracle",
  "hostspec" => "www.stanford.edu",
  "database" => "cs145db",
  "username" => "ullman",
  "password" => "notMyPW" );

建立连接

在导入 DB library 及上面的 $myEnv 数组变量后:

$myCon = DB::connect($myEnv);

DB::connect() 表示应用 DB library 中的函数 connect.
$myCon 是 Connection 类, 因为它的值是由 DB::connect() 返回.

执行 SQL 语句

query 函数(方法)应用到连接对象(Connection object)上.

它的参数是字符串. 当发生错误时返回一个错误码, 当成功时返回一个关系.

例子: 执行 SQL 语句

对于给定的变量 $beer, 在关系 Sells 中查询所有销售这种啤酒的酒吧.

$beer = 'Bud';
$result = $myCon->query(
  "SELECT bar FROM Sells " .
  " WHERE beer = $beer;");

$myCon->query 表示应用对象 $myCon 的方法 query.
. 是字符串的连接操作.
$beer 在双引号中, 因此被它的值所代替.

PHP 中的游标(Cursor)

PHP 中的游标

query 成功返回的结果是一个关系, 可能包含了多个元组.

fetchRow 这个方法(函数)应用在 query 所得的关系上, 返回下一行, 如果没有下一行, 则返回 FALSE.

例子: 游标

while ($bar=$result->fetchRow()){
	//do something with $bar
}

PHP 连接 MySQL 的例子

使用mysqli

<?php
$servername = "localhost";
$username = "root";
$password = "34klq*";//更改为你使用的mysql服务器密码
$dbname = "test_bar";//"urlsdb";

$conn = new mysqli($servername, $username, $password, $dbname);

echo "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">";
echo "<html>";
echo "<head><title>测试连接MySQL</title>";
echo "<meta http-equiv=\"content-type\" content=\"text/html; charset=utf-8\" />";
echo "</head><body>";

if ($conn->connect_error){
    die("连接失败: ".$conn->connect_error);
}else{
	
	print("Successfully connected to MySQL! <br/>The database is: <b>".$dbname."</b><br/><br/><hr/>");

	@mysqli_query($conn, "set names 'utf8'");//最好加一下，不然会有乱码
	@mysqli_select_db($dbname, $conn);

	$sql2="SELECT * FROM Bars";
	$result=mysqli_query($conn,$sql2);
	echo "<table border=1>";
	while($row=mysqli_fetch_assoc($result))
	{
		echo "<tr><td>".$row['name']."</td>";
		echo "<td>".$row['addr']."</td>";
		echo "<td>".$row['license']."</td></tr>";
	}
	echo "</table>";
}


echo "</body></html>";
?>

首页

Haifeng Xu

(hfxu@yzu.edu.cn)

目录

SQL 注入(SQL Injection)

SQL 注入(SQL Injection)

例子: SQL 注入