sql注入攻擊
SQL注入攻擊是一種常見的網絡安全威脅,攻擊者通過在輸入字段中插入惡意SQL代碼,繞過應用程序的安全機制,直接操縱數據庫。
SQL注入的原理
SQL注入利用應用程序未對用戶輸入進行充分過濾或轉義的漏洞。當用戶輸入被直接拼接到SQL查詢中時,攻擊者可以構造特殊輸入,改變查詢邏輯,獲取未授權的數據或執行惡意操作。
-- 示例:通過輸入' OR '1'='1繞過登錄驗證
SELECT * FROM users WHERE username = 'admin' AND password = '' OR '1'='1';
SQL注入攻擊是一種常見的網絡安全威脅,攻擊者通過在輸入字段中插入惡意SQL代碼,繞過應用程序的安全機制,直接操縱數據庫。以下是關于SQL注入攻擊的詳細說明:
常見的SQL注入類型
基于錯誤的注入
攻擊者通過故意引發數據庫錯誤,從錯誤信息中獲取數據庫結構或敏感數據。
聯合查詢注入
利用UNION操作符將惡意查詢結果合并到正常查詢結果中,獲取其他表的數據。
-- 示例:獲取其他表數據
SELECT id, name FROM products WHERE id = 1 UNION SELECT username, password FROM users;
盲注
當應用程序不返回錯誤信息時,攻擊者通過布爾條件或時間延遲判斷查詢結果。
-- 布爾盲注示例
SELECT * FROM users WHERE username = 'admin' AND SUBSTRING(password, 1, 1) = 'a';
防御SQL注入的方法
參數化查詢(預編譯語句)
使用參數化查詢確保用戶輸入始終被視為數據而非代碼。
# Python示例(使用SQLite)
import sqlite3
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users WHERE username = ? AND password = ?", (username, password))
輸入驗證與過濾
對用戶輸入進行嚴格驗證,僅允許符合預期的字符或格式。
最小權限原則
數據庫賬戶應僅具有必要的最小權限,避免使用高權限賬戶連接數據庫。
使用ORM框架
ORM(如Django ORM、SQLAlchemy)自動處理參數化查詢,減少手動拼接SQL的需求。
# Django ORM示例
from django.db import models
User.objects.filter(username=username, password=password)
靜態SQL與動態SQL的區別
靜態SQL和動態SQL是數據庫編程中兩種不同的SQL語句處理方式,它們在編寫方式、執行效率和適用場景上有顯著差異。
靜態SQL
靜態SQL是指在程序編譯時就已確定的SQL語句,通常直接嵌入在源代碼中。這些語句的結構和參數在編譯時已知,數據庫管理系統(DBMS)可以預先優化。
特點
- 語句在編譯時固定,無法在運行時改變。
- 性能較高,因為DBMS可以預編譯和優化。
- 通常用于參數化查詢,參數通過占位符(如
?
或@param
)傳遞。
示例代碼(Java中使用JDBC)
String sql = "SELECT * FROM employees WHERE department_id = ?";
PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setInt(1, 10); // 設置參數
ResultSet rs = stmt.executeQuery();
動態SQL
動態SQL是指在程序運行時動態構建的SQL語句,通常根據用戶輸入或其他運行時條件生成。語句的結構或內容可能在運行時變化。
特點
- 語句在運行時動態生成,靈活性高。
- 性能較低,因為DBMS無法預編譯。
- 容易引發SQL注入風險,需謹慎處理輸入。
示例代碼(Java中使用JDBC)
String departmentId = getUserInput(); // 用戶輸入
String sql = "SELECT * FROM employees WHERE department_id = " + departmentId;
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(sql);
安全性與性能對比
靜態SQL
- 安全性高:參數化查詢避免SQL注入。
- 性能優:預編譯減少運行時開銷。
動態SQL
- 安全性低:直接拼接輸入可能導致注入。
- 性能差:每次執行需重新解析和優化。
例子
數據庫設計:
實體類代碼:
package com.qcby.entity;public class student {private Integer id;private String name;private String sex;private String phone;private String password;private Integer age;private Integer pageSize;private Integer pageStart;public student() {}public student(String name, String sex, String phone, Integer age) {this.name = name;this.sex = sex;this.phone = phone;this.age = age;}public Integer getPageSize() {return pageSize;}public void setPageSize(Integer pageSize) {this.pageSize = pageSize;}public Integer getPageStart() {return pageStart;}public void setPageStart(Integer pageStart) {this.pageStart = pageStart;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}public String getPhone() {return phone;}public void setPhone(String phone) {this.phone = phone;}public String getPassword() {return password;}public void setPassword(String password) {this.password = password;}public Integer getAge() {return age;}public void setAge(Integer age) {this.age = age;}@Overridepublic String toString() {return "student{" +"id=" + id +", name='" + name + '\'' +", sex='" + sex + '\'' +", phone='" + phone + '\'' +", password='" + password + '\'' +", age=" + age +'}';}
}
dao層代碼
package com.qcby.dao;import com.qcby.entity.student;
import org.apache.ibatis.annotations.Param;import java.util.List;public interface StudentDao {public abstract List<student> findAll();student findById(Integer id);Integer delete(Integer id);List<student> findByName(String name);List<student> findBystudent(student student);Integer insert(student student);Integer insertGetId(student student);List<student> findAllStudent(Integer pageSize,Integer pageStart);List<student> findAllStudentByAge (student student);List<student> findUserByStudentName(String name);List<student> findUser(student student);int update(student student);List<student> selectUserByChoose(student student);List<student> selectUserByUsernameAndSex(student student);int trimUpdate(student student);int deleteMoreByArray(@Param("ids") Integer[] ids);int insertMoreByList(@Param("students") List<student> students);
}
mapper.xml代碼
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qcby.dao.StudentDao"><select id="findAll" resultType="com.qcby.entity.student">SELECT * FROM student</select><select id="findById" resultType="com.qcby.entity.student" parameterType="java.lang.Integer">SELECT * from student WHERE id=#{id}</select><delete id="delete" parameterType="java.lang.Integer">DELETE from student WHERE id=#{id}</delete><select id="findByName" resultType="com.qcby.entity.student" parameterType="java.lang.String">SELECT * from student where name=#{name}</select><select id="findBystudent" resultType="com.qcby.entity.student" parameterType="com.qcby.entity.student">SELECT * from student where name=#{name} and password=#{password}</select><insert id="insert" parameterType="com.qcby.entity.student">INSERT INTO student(name,sex,phone,password,age) VALUES (#{name},#{sex},#{phone},#{password},#{age})</insert><insert id="insertGetId" parameterType="com.qcby.entity.student"><selectKey keyProperty="id" resultType="int" order="AFTER">select LAST_INSERT_ID();</selectKey>INSERT INTO student(name,sex,phone,password,age) VALUES (#{name},#{sex},#{phone},#{password},#{age})</insert><select id="findAllStudent" parameterType="java.lang.Integer" resultType="com.qcby.entity.student">SELECT * from student limit #{param1} offset #{param2}</select><select id="findAllStudentByAge" resultType="com.qcby.entity.student" parameterType="com.qcby.entity.student">select * from student WHERE name=#{name} limit #{pageSize} offset #{pageStart}</select><select id="findUserByStudentName" parameterType="java.lang.String" resultType="com.qcby.entity.student">select * from student where name like #{value}</select><!-- where if --><select id="findUser" resultType="com.qcby.entity.student" parameterType="com.qcby.entity.student">select * from student<where><if test="name!=null and name!=''">AND name=#{name}</if><if test="sex!=null and sex!=''">AND sex=#{sex}</if><if test="phone!=null and phone!=''">AND phone=#{phone}</if><if test="password!=null and password!=''">AND password=#{password}</if><if test="age!=null and age!=''">AND age=#{age}</if></where></select><update id="update" parameterType="com.qcby.entity.student">UPDATE student<set><if test="name!=null and name!=''">name=#{name},</if><if test="sex!=null and sex!=''">sex=#{sex},</if><if test="phone!=null and phone!=''">phone=#{phone},</if><if test="password!=null and password!=''">password=#{password},</if><if test="age!=null and age!=''">age=#{age}</if></set>WHERE id = #{id}</update>
<!--if elseif else--><select id="selectUserByChoose" resultType="com.qcby.entity.student" parameterType="com.qcby.entity.student">select * from student<where><choose><when test="name!=null and name!=''">AND name=#{name}</when><when test="sex!=null and sex!=''">AND sex=#{sex}</when><otherwise>and id=#{id}</otherwise></choose></where></select><select id="selectUserByUsernameAndSex" resultType="com.qcby.entity.student" parameterType="com.qcby.entity.student">SELECT * from student<trim prefix="where" prefixOverrides="and | or"><if test="name!=null and name!=''">AND name=#{name}</if><if test="sex!=null and sex!=''">AND sex=#{sex}</if><if test="phone!=null and phone!=''">AND phone=#{phone}</if><if test="password!=null and password!=''">AND password=#{password}</if><if test="age!=null and age!=''">AND age=#{age}</if></trim></select>
<update id="trimUpdate" parameterType="com.qcby.entity.student">update student<trim prefix="set" suffixOverrides=","><if test="name!=null and name!=''">name=#{name},</if><if test="sex!=null and sex!=''">sex=#{sex},</if><if test="phone!=null and phone!=''">phone=#{phone},</if><if test="password!=null and password!=''">password=#{password},</if><if test="age!=null and age!=''">age=#{age},</if></trim>where id = #{id}
</update><!--delete from user where id in (1,2,3,4,5); --><delete id="deleteMoreByArray">DELETE FROM student WHERE id IN<foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach></delete><!-- collection:當前要循環的數組或者集合 --><!-- item: 我們指定要循環的數組的每一個元素 --><!-- separator:每一個元素應該用什么來做分割 --><!-- open:當前循環是以什么開始 --><!-- close:當前循環是以什么結束 --><!--insert into 表名 (字段) values (值),(值)--><insert id="insertMoreByList" >insert into student(name,age,sex,phone) values<foreach collection="students" item="student" separator=",">(#{student.name},#{student.age},#{student.sex},#{student.phone})</foreach></insert>
</mapper>
1. 基本結構與命名空間
<mapper namespace="com.qcby.dao.StudentDao">
這個標簽確定了命名空間為com.qcby.dao.StudentDao
,這意味著在 Java 代碼里可以通過該命名空間來調用這些 SQL 映射語句。
2. 基礎 CRUD 操作
- 查詢全部學生:
此語句會返回<select id="findAll" resultType="com.qcby.entity.student">SELECT * FROM student </select>
student
表中的所有記錄。 - 按 ID 查詢學生:
它依據傳入的 ID 參數來查找對應的學生。<select id="findById" resultType="com.qcby.entity.student" parameterType="java.lang.Integer">SELECT * from student WHERE id=#{id} </select>
- 插入學生:
該語句用于向表中插入新的學生記錄。<insert id="insert" parameterType="com.qcby.entity.student">INSERT INTO student(name,sex,phone,password,age) VALUES (#{name},#{sex},#{phone},#{password},#{age}) </insert>
- 更新學生信息:
借助<update id="update" parameterType="com.qcby.entity.student">UPDATE student<set><if test="name!=null and name!=''">name=#{name},</if><!-- 其他字段的更新條件類似 --></set>WHERE id = #{id} </update>
<set>
標簽和<if>
條件判斷,能夠動態地更新學生信息。 - 刪除學生:
此語句會刪除指定 ID 的學生記錄。<delete id="delete" parameterType="java.lang.Integer">DELETE from student WHERE id=#{id} </delete>
3. 動態 SQL 元素
<where>
標簽:
當有條件滿足時,<select id="findUser" resultType="com.qcby.entity.student" parameterType="com.qcby.entity.student">select * from student<where><if test="name!=null and name!=''">AND name=#{name}</if><!-- 其他條件判斷 --></where> </select>
<where>
標簽會自動添加WHERE
關鍵字,并且能智能地去除多余的AND
或OR
。<choose>
、<when>
、<otherwise>
標簽:
這組標簽類似于 Java 中的<select id="selectUserByChoose" resultType="com.qcby.entity.student" parameterType="com.qcby.entity.student">select * from student<where><choose><when test="name!=null and name!=''">AND name=#{name}</when><otherwise>and id=#{id}</otherwise></choose></where> </select>
switch-case
語句,會按順序進行條件判斷,一旦有條件滿足就會停止后續判斷。<trim>
標簽:<update id="trimUpdate" parameterType="com.qcby.entity.student">update student<trim prefix="set" suffixOverrides=","><!-- 更新字段 --></trim>where id = #{id} </update>
<trim>
標簽可以自定義前綴和后綴,suffixOverrides
屬性能夠移除多余的逗號。
4. 批量操作
- 批量刪除:
<delete id="deleteMoreByArray">DELETE FROM student WHERE id IN<foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach> </delete>
<foreach>
標簽會對集合進行遍歷,可用于生成IN
條件。 - 批量插入:
利用<insert id="insertMoreByList" >insert into student(name,age,sex,phone) values<foreach collection="students" item="student" separator=",">(#{student.name},#{student.age},#{student.sex},#{student.phone})</foreach> </insert>
<foreach>
標簽能夠批量插入多條學生記錄。
5. 分頁查詢
<select id="findAllStudent" parameterType="java.lang.Integer" resultType="com.qcby.entity.student">SELECT * from student limit #{param1} offset #{param2}
</select>
此查詢通過limit
和offset
實現分頁功能,param1
代表每頁的記錄數,param2
代表偏移量。
6. 自動獲取主鍵
<insert id="insertGetId" parameterType="com.qcby.entity.student"><selectKey keyProperty="id" resultType="int" order="AFTER">select LAST_INSERT_ID();</selectKey>INSERT INTO student(name,sex,phone,password,age) VALUES (#{name},#{sex},#{phone},#{password},#{age})
</insert>
<selectKey>
標簽的作用是在插入數據后獲取自動生成的主鍵,并將其賦值給實體類的id
屬性。
Test代碼
import com.qcby.dao.StudentDao;
import com.qcby.dao.UserDao;
import com.qcby.entity.User;
import com.qcby.entity.student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.List;
public class StudentTest {private InputStream in = null;private SqlSession session = null;private StudentDao mapper = null;@Before //前置通知, 在方法執行之前執行public void init() throws IOException {//加載主配置文件,目的是為了構建SqlSessionFactory對象in = Resources.getResourceAsStream("SqlMapConfig.xml");//創建SqlSessionFactory對象SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);//通過SqlSessionFactory工廠對象創建SqlSesssion對象session = factory.openSession();//通過Session創建UserDao接口代理對象mapper = session.getMapper(StudentDao.class);}@After //@After: 后置通知, 在方法執行之后執行 。public void destory() throws IOException {//釋放資源session.close();in.close();}@Testpublic void SELETALL(){List<student> students = mapper.findAll();for (student student: students) {System.out.println(student.toString());}}@Testpublic void findById(){student student=mapper.findById(1);System.out.println(student.toString());}@Testpublic void delete(){mapper.delete(1);List<student> students = mapper.findAll();for (student student: students) {System.out.println(student.toString());}session.commit();}@Testpublic void findByName(){List<student> students=mapper.findByName("楊");for(student students1:students){System.out.println(students1.toString());}}@Testpublic void findBystudent(){student student=new student();student.setName("yu");student.setPassword("123456");List<student> students=mapper.findBystudent(student);for (student student1: students) {System.out.println(student1.toString());}}
@Testpublic void insert(){student student=new student();student.setName("11");student.setPassword("123");student.setAge(11);student.setPhone("12345");student.setSex("男");mapper.insert(student);List<student> students = mapper.findAll();for (student student1: students) {System.out.println(student1.toString());}session.commit();}@Testpublic void insertGetId(){student student=new student();student.setName("11");student.setPassword("123");student.setAge(11);student.setPhone("12345");student.setSex("男");mapper.insertGetId(student);List<student> students = mapper.findAll();for (student student1: students) {System.out.println(student1.toString());}session.commit();}@Testpublic void findAllStudent(){Integer pageSize=5;Integer pageIndex =1;Integer pageStart =pageSize *(pageIndex-1);List<student> students =mapper.findAllStudent(pageSize,pageStart);for(student student:students){System.out.println(student.toString());}}@Testpublic void findAllStudentByAge(){Integer pageSize=5;Integer pageIndex =1;student student=new student();Integer pageStart =pageSize *(pageIndex-1);student.setPageSize(pageSize);student.setName("博");student.setPageStart(pageStart);List<student> students =mapper.findAllStudentByAge(student);for(student student1:students){System.out.println(student1.toString());}}@Testpublic void findUserByStudentName(){List<student> students=mapper.findUserByStudentName("%楊%");for(student student1:students){System.out.println(student1.toString());}}@Testpublic void findUser(){student student=new student();student.setSex("男");student.setPhone("1234321");student.setAge(11);student.setName("zzz");student.setPassword("123321");List<student> students=mapper.findUser(student);for(student student1:students){System.out.println(student1.toString());}}@Test
public void update(){student student=new student();student.setName("zzz");student.setId(2);student.setPassword("11441276423");student.setAge(143);student.setPhone("14231312");student.setSex("男");Integer num=mapper.update(student);List<student> students=mapper.findUser(student);for(student student1:students){System.out.println(student1.toString());}System.out.println(num);}@Test
public void selectUserByChoose(){student student=new student();student.setId(2);student.setName("bo");List<student>students=mapper.selectUserByChoose(student);for(student student1:students){System.out.println(student1.toString());}
}@Test
public void selectUserByUsernameAndSex(){student student=new student();student.setSex("男");student.setName("bo");List<student>students=mapper.selectUserByUsernameAndSex(student);for(student student1:students){System.out.println(student1.toString());}}@Test
public void trimUpdate(){student student=new student();student.setName("sssssss");student.setId(2);mapper.trimUpdate(student);}@Testpublic void deleteMoreByArray(){Integer[] integer = new Integer[]{11,12,10,11};mapper.deleteMoreByArray(integer);session.commit();}@Testpublic void insertMoreByList(){student user1 = new student("小趙","男","1234",11);student user2 = new student("小張","男","122234",4);student user3 = new student("小李","男","123334",31);List<student> users = Arrays.asList(user1,user2,user3);mapper.insertMoreByList(users);session.commit();}}
單元測試基礎
單元測試是驗證代碼最小單元(如函數、方法)行為的自動化測試。以下以Java的JUnit框架為例說明基本用法:
import org.junit.jupiter.api.Test;
import static org.junit.jupiter.api.Assertions.assertEquals;public class CalculatorTest {@Testpublic void testAddition() {Calculator calc = new Calculator();assertEquals(5, calc.add(2, 3)); // 驗證2+3=5}
}
測試注解類型
@Test
:標記方法為測試用例@BeforeEach
:每個測試前執行初始化@AfterEach
:每個測試后執行清理@BeforeAll
/@AfterAll
:全局初始化和清理(靜態方法)
斷言方法示例
import static org.junit.jupiter.api.Assertions.*;assertTrue(result > 0); // 驗證條件為真
assertNull(someObject); // 驗證對象為空
assertArrayEquals(expected, actual); // 驗證數組相等
參數化測試
通過@ParameterizedTest
實現多組輸入測試:
@ParameterizedTest
@ValueSource(ints = {1, 2, 3})
void testIsPositive(int number) {assertTrue(number > 0);
}
測試覆蓋率
使用工具(如JaCoCo)統計代碼被測試覆蓋的比例,通常建議達到80%以上關鍵路徑覆蓋率。在Maven項目中配置示例:
<plugin><groupId>org.jacoco</groupId><artifactId>jacoco-maven-plugin</artifactId><version>0.8.7</version><executions><execution><goals><goal>prepare-agent</goal></goals></execution></executions>
</plugin>
?Arrays.asList
Arrays.asList 方法
Arrays.asList
是 Java 中 java.util.Arrays
類提供的一個靜態方法,用于將數組或可變參數轉換為一個固定大小的列表(List
)。該方法返回的列表是基于原始數組的視圖,因此對列表的修改會影響原始數組,反之亦然。
語法
public static <T> List<T> asList(T... a)
使用示例
以下是一些常見的 Arrays.asList
用法示例:
示例 1:將數組轉換為列表
String[] stringArray = {"apple", "banana", "cherry"};
List<String> stringList = Arrays.asList(stringArray);
System.out.println(stringList); // 輸出: [apple, banana, cherry]
示例 2:直接傳遞可變參數
List<String> list = Arrays.asList("one", "two", "three");
System.out.println(list); // 輸出: [one, two, three]
注意事項
-
固定大小列表
返回的列表是固定大小的,不能添加或刪除元素,否則會拋出UnsupportedOperationException
。List<String> list = Arrays.asList("a", "b", "c"); list.add("d"); // 拋出 UnsupportedOperationException
-
修改會影響原始數組
由于返回的列表是原始數組的視圖,修改列表中的元素會影響原始數組。String[] arr = {"a", "b", "c"}; List<String> list = Arrays.asList(arr); list.set(0, "x"); // 修改列表 System.out.println(arr[0]); // 輸出: x