校园图书馆管理系统查询借阅书籍时出现MySQL语法错误求助

错误根源 #

你的SQL语法错误来自字符串拼接时的代码书写错误：创建MySqlCommand时，错误地将db.getConnection()的调用部分当成了SQL字符串的一部分，导致数据库收到的SQL语句在b.borrower ='1'之后出现无效字符，触发语法报错。

同时，直接将login_user.id拼接进SQL语句还存在SQL注入风险，这是必须规避的安全问题。

修复后的代码 #

核心修正：修正拼接错误+改用参数化查询 #

db.openConnection();
// 使用参数化查询避免SQL注入，同时修正字符串拼接错误
command = new MySqlCommand(@"SELECT a.img_drt, a.title, a.author,
a.genre, a.publisher, a.yearpub, a.isbn, b.borrow_date 
FROM library a
INNER JOIN user_borrow b ON a.id = b.book 
WHERE b.borrower = @UserId", db.getConnection());
// 通过参数传递用户ID，替代直接拼接
command.Parameters.AddWithValue("@UserId", login_user.id);

reader = command.ExecuteReader();
while (reader.Read())
{
    long len = reader.GetBytes(0, 0, null, 0, 0);
    byte[] array = new byte[Convert.ToInt32(len) + 1];
    reader.GetBytes(0, 0, array, 0, Convert.ToInt32(len));
    pictureBox = new PictureBox();
    pictureBox.Width = 209;
    pictureBox.Height = 317;
    pictureBox.Location = new Point(12, 14);
    pictureBox.BackgroundImageLayout = ImageLayout.Stretch;
    System.IO.MemoryStream ms = new System.IO.MemoryStream(array);
    pictureBox.BackgroundImage = Image.FromStream(ms, true, true);

    panel = new Panel();
    panel.Location = new Point(25, 29);
    panel.Width = 551;
    panel.Height = 346;
    panel.Margin = new Padding(25, 29, 70, 56);
    panel.BackColor = Color.FromArgb(0, 166, 251);

    title = new Label();
    title.Text = reader["title"].ToString().ToUpper();
    title.AutoSize = true;
    title.MaximumSize = new Size(267, 87);
    title.Font = new Font("Inter Black", 11);
    title.ForeColor = Color.Black;
    title.Location = new Point(254, 14);

    author = new Label();
    author.Text = "BY: " + reader["author"].ToString().ToUpper();
    author.AutoSize = true;
    author.MaximumSize = new Size(267, 45);
    author.Font = new Font("Inter SemiBold", 9);
    author.ForeColor = Color.Black;
    author.Location = new Point(254, 119);

    genre = new Label();
    genre.Text = "GENRE: " + reader["genre"].ToString().ToUpper();
    genre.Size = new Size(284, 72);
    genre.Font = new Font("Inter SemiBold", 9);
    genre.ForeColor = Color.Black;
    genre.Location = new Point(254, 165);

    published = new Label();
    published.Text = "YEAR PUBLISHED: " + reader["yearpub"].ToString().ToUpper();
    published.Size = new Size(284, 24);
    published.Font = new Font("Inter SemiBold", 9);
    published.ForeColor = Color.Black;
    published.Location = new Point(254, 239);

    panel.Controls.Add(pictureBox);
    panel.Controls.Add(title);
    panel.Controls.Add(author);
    panel.Controls.Add(genre);
    panel.Controls.Add(published);
    flowLayout.Controls.Add(panel);
}
reader.Close();
db.closeConnection();

关键修正点 #

1. 修复拼接错误：移除了SQL字符串末尾错误的", " +，将db.getConnection()作为MySqlCommand的第二个参数正确传入，而非SQL语句的一部分。
2. 替换为参数化查询：用@UserId占位符替代直接拼接用户ID，通过Parameters.AddWithValue传递参数值，既彻底避免SQL注入，也避免了因ID数据类型（如数字类型）导致的引号匹配问题。

验证方式 #

执行ExecuteReader()前，可通过command.CommandText查看生成的SQL语句，确认格式正确：

Console.WriteLine(command.CommandText);

正确的SQL语句应为：

SELECT a.img_drt, a.title, a.author,
a.genre, a.publisher, a.yearpub, a.isbn, b.borrow_date 
FROM library a
INNER JOIN user_borrow b ON a.id = b.book 
WHERE b.borrower = @UserId

内容的提问来源于stack exchange，提问作者June