{"id":2224,"date":"2015-06-07T23:58:54","date_gmt":"2015-06-07T15:58:54","guid":{"rendered":"http:\/\/ixyzero.com\/blog\/?p=2224"},"modified":"2015-06-07T23:58:54","modified_gmt":"2015-06-07T15:58:54","slug":"python%e4%b8%ad%e7%9a%84%e9%98%b2sql%e6%b3%a8%e5%85%a5","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/2224.html","title":{"rendered":"Python\u4e2d\u7684\u9632SQL\u6ce8\u5165"},"content":{"rendered":"<h6>\u641c\u7d22\u5173\u952e\u5b57\uff1a<\/h6>\n<ul>\n<li>python mysqldb anti sql injection<\/li>\n<\/ul>\n<h6>\u53c2\u8003\u94fe\u63a5\uff1a<\/h6>\n<ul>\n<li><a href=\"http:\/\/bobby-tables.com\/python.html\">http:\/\/bobby-tables.com\/python.html<\/a><\/li>\n<li><a href=\"https:\/\/wiki.python.org\/moin\/DbApiFaq\">https:\/\/wiki.python.org\/moin\/DbApiFaq<\/a><\/li>\n<li><a href=\"https:\/\/www.python.org\/dev\/peps\/pep-0249\/#paramstyle\">https:\/\/www.python.org\/dev\/peps\/pep-0249\/#paramstyle<\/a><\/li>\n<li><a href=\"https:\/\/docs.python.org\/2\/library\/sqlite3.html#sqlite3.Cursor.execute\">https:\/\/docs.python.org\/2\/library\/sqlite3.html#sqlite3.Cursor.execute<\/a><\/li>\n<li>=<\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/7929364\/python-best-practice-and-securest-to-connect-to-mysql-and-execute-queries\">http:\/\/stackoverflow.com\/questions\/7929364\/python-best-practice-and-securest-to-connect-to-mysql-and-execute-queries<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/3617052\/escape-string-python-for-mysql\">http:\/\/stackoverflow.com\/questions\/3617052\/escape-string-python-for-mysql<\/a><\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/775296\/python-mysql-with-variables\">http:\/\/stackoverflow.com\/questions\/775296\/python-mysql-with-variables<\/a><\/li>\n<li>=<\/li>\n<li><a href=\"http:\/\/xlixli.net\/?p=377\" target=\"_blank\">[Python]Python\u9884\u7f16\u8bd1\u8bed\u53e5\u9632\u6b62SQL\u6ce8\u5165<\/a><\/li>\n<li>=<\/li>\n<li><a href=\"http:\/\/www.sqlinjectionwiki.com\/Categories\/5\/references\/\" target=\"_blank\">SQL Injection Wiki | References<\/a><\/li>\n<li>=<\/li>\n<li><a href=\"http:\/\/stackoverflow.com\/questions\/9059027\/if-i-receive-post-data-with-flask-put-that-data-into-a-wtforms-form-and-it-succ\" target=\"_blank\">http:\/\/stackoverflow.com\/questions\/9059027\/if-i-receive-post-data-with-flask-put-that-data-into-a-wtforms-form-and-it-succ<\/a><\/li>\n<\/ul>\n<h6>\u53c2\u8003\u89e3\u7b54\uff1a<\/h6>\n<p>Using the <a href=\"http:\/\/wiki.python.org\/moin\/DatabaseProgramming\/\" target=\"_blank\">Python DB API<\/a>, don&#8217;t do this:<\/p>\n<pre class=\"lang:default decode:true\"># Do NOT do it this way.\ncmd = \"update people set name='%s' where id='%s'\" % (name, id)\ncurs.execute(cmd)<\/pre>\n<p>Instead, do this:<\/p>\n<pre class=\"lang:default decode:true\">cmd = \"update people set name=%s where id=%s\"\ncurs.execute(cmd, (name, id))<\/pre>\n<p>Note that the placeholder syntax depends on the database you are using.<\/p>\n<pre class=\"lang:default decode:true  \">'qmark'     Question mark style,\n                e.g. '...WHERE name=?'\n'numeric'   Numeric, positional style,\n                e.g. '...WHERE name=:1'\n'named'     Named style,\n                e.g. '...WHERE name=:name'\n'format'    ANSI C printf format codes,\n                e.g. '...WHERE name=%s'\n'pyformat'  Python extended format codes,\n                e.g. '...WHERE name=%(name)s'<\/pre>\n<p>The values for the most common databases are:<\/p>\n<pre class=\"lang:default decode:true\">&gt;&gt;&gt; import MySQLdb; print MySQLdb.paramstyle\nformat\n&gt;&gt;&gt; import psycopg2; print psycopg2.paramstyle\npyformat\n&gt;&gt;&gt; import sqlite3; print sqlite3.paramstyle\nqmark<\/pre>\n<p>So if you are using MySQL or PostgreSQL, use <code>%s<\/code> (even for numbers and other non-string values!) and if you are using SQLite use <code>?<\/code><\/p>\n<p>==<\/p>\n<h2 id=\"How_do_I_pass_parameters_to_the_cursor.execute_method.3F\">How do I pass parameters to the cursor.execute method?<\/h2>\n<p class=\"line874\">Don&#8217;t use the &#8216;%&#8217; concatenation operator, pass them as a series of extra parameters. For instance<span id=\"line-7\" class=\"anchor\"><\/span><span id=\"line-8\" class=\"anchor\"><\/span><\/p>\n<p class=\"line867\"><tt>&gt;&gt;&gt;\u00a0cursor.execute(\"SELECT\u00a0*\u00a0FROM\u00a0my_table\u00a0WHERE\u00a0my_column\u00a0=\u00a0'%s'\"\u00a0%\u00a0\"column_value\")\u00a0<\/tt><span id=\"line-9\" class=\"anchor\"><\/span><span id=\"line-10\" class=\"anchor\"><\/span><\/p>\n<p class=\"line874\">May do what you want, but more by accident than design. If you change it to;<span id=\"line-11\" class=\"anchor\"><\/span><span id=\"line-12\" class=\"anchor\"><\/span><\/p>\n<p class=\"line867\"><tt>&gt;&gt;&gt;\u00a0cursor.execute(\"SELECT\u00a0*\u00a0FROM\u00a0my_table\u00a0WHERE\u00a0my_column\u00a0=\u00a0%s\",\u00a0\"column_value\")\u00a0<\/tt><span id=\"line-13\" class=\"anchor\"><\/span><span id=\"line-14\" class=\"anchor\"><\/span><\/p>\n<p class=\"line874\">Then the DB-API module will make sure your value is correctly escaped and turned into an object appropriate for the database.<\/p>\n<p>==<\/p>\n<p><strong>execute()\u51fd\u6570\u672c\u8eab\u5c31\u6709\u63a5\u53d7SQL\u8bed\u53e5\u53d8\u91cf\u7684\u53c2\u6570\u4f4d\uff0c\u53ea\u8981\u6b63\u786e\u7684\u4f7f\u7528\uff08\u76f4\u767d\u4e00\u70b9\u5c31\u662f\uff1a<span style=\"color: #ff0000;\">\u4f7f\u7528&#8221;\u9017\u53f7&#8221;\uff0c\u800c\u4e0d\u662f&#8221;\u767e\u5206\u53f7&#8221;<\/span>\uff09\u5c31\u53ef\u4ee5\u5bf9\u4f20\u5165\u7684\u503c\u8fdb\u884ccorrectly\u8f6c\u4e49\uff0c\u4ece\u800c\u907f\u514dSQL\u6ce8\u5165\u7684\u53d1\u751f\u3002<\/strong><\/p>\n<p>==<\/p>\n<p style=\"padding-left: 30px;\"><tt class=\"descname\">execute<\/tt><big>(<\/big><em>sql<\/em><span class=\"optional\">[<\/span>, <em>parameters<\/em><span class=\"optional\">]<\/span><big>)<\/big><\/p>\n<p>Executes an SQL statement. The SQL statement may be parameterized (i. e. placeholders instead of SQL literals). The <a class=\"reference internal\" title=\"sqlite3: A DB-API 2.0 implementation using SQLite 3.x.\" href=\"https:\/\/docs.python.org\/2\/library\/sqlite3.html#module-sqlite3\"><tt class=\"xref py py-mod docutils literal\"><span class=\"pre\">sqlite3<\/span><\/tt><\/a>module supports two kinds of placeholders: question marks (qmark style) and named placeholders (named style).<\/p>\n<p>Here\u2019s an example of both styles:<\/p>\n<pre class=\"lang:default decode:true\">import sqlite3\n\ncon = sqlite3.connect(\":memory:\")\ncur = con.cursor()\ncur.execute(\"create table people (name_last, age)\")\n\nwho = \"Yeltsin\"\nage = 72\n\n# This is the qmark style:\ncur.execute(\"insert into people values (?, ?)\", (who, age))\n\n# And this is the named style:\ncur.execute(\"select * from people where name_last=:who and age=:age\", {\"who\": who, \"age\": age})\n\nprint cur.fetchone()<\/pre>\n<p><a class=\"reference internal\" title=\"sqlite3.Cursor.execute\" href=\"https:\/\/docs.python.org\/2\/library\/sqlite3.html#sqlite3.Cursor.execute\"><tt class=\"xref py py-meth docutils literal\"><span class=\"pre\">execute()<\/span><\/tt><\/a> will only execute a single SQL statement. If you try to execute more than one statement with it, it will raise a Warning. Use <a class=\"reference internal\" title=\"sqlite3.Cursor.executescript\" href=\"https:\/\/docs.python.org\/2\/library\/sqlite3.html#sqlite3.Cursor.executescript\"><tt class=\"xref py py-meth docutils literal\"><span class=\"pre\">executescript()<\/span><\/tt><\/a> if you want to execute multiple SQL statements with one call.<\/p>\n<p>=EOF=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u641c\u7d22\u5173\u952e\u5b57\uff1a python mysqldb anti sql injection \u53c2\u8003\u94fe\u63a5\uff1a http:\/\/b [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,7,25,12],"tags":[370,8],"class_list":["post-2224","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-programing","category-security","category-tools","tag-mysqldb","tag-python"],"views":6855,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2224","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=2224"}],"version-history":[{"count":0,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/2224\/revisions"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=2224"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=2224"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=2224"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}