CERIAS - Center for Education and Research in Information Assurance and Security

Skip Navigation
Purdue University
Center for Education and Research in Information Assurance and Security


Page Content

Beware SQL libraries missing prepared statement support

Just because your library or framework allows you to specify an SQL query and the data separately, doesn't mean that it's sending data separately from code to the database.

Imagine this scenario. You read that prepared statements are a good way to avoid SQL injection, because the database is given code and data explicitly and separately. You chose a database that supports prepared statements. The library you use also seems to support them as you can pass SQL code and data as two separate arguments. However... internally the library just constructs a string and sends that to the database, and doesn't use the database's prepared statement support!

An example is the library "pyPGSQL", which supports PostGreSQL in Python. It has an "execute" command taking a query and parameters as separate arguments. However, internally it constructs a string to send off (after escaping the parameters, so it *shouldn't* be vulnerable):
self.res = self.conn.conn.query(_qstr % parms)

The point is that escaping on the client side, while most likely OK, isn't as robust as letting the database handle the data separately, by using prepared statements. This particularity of pyPGSQL has been known since 2003 (forum answer). However, it's good to point it out again, as I had started writing a program using pyPGSQL, thinking that my code would be fine. It's possible that others have as well. pyPGSQL doesn't claim to support prepared statements (the absence of a "prepare" instruction should have been a clue!). Nevertheless, it surprises me that there still exist libraries not supporting prepared statements and that don't state this with an unmistakable, large warning. I found surprisingly few Python libraries supporting prepared statements:
  • py-postgresql (unfortunately requires Python 3; was pg_proboscis for Python 2)
  • Cristian Gafton's python-pgsql (not thread-safe)
I am not going to discuss why attempting to escape data in an SQL statement is complex and error-prone, and why prepared statements are a more secure alternative; this has been addressed elsewhere and supported by vulnerability announcements. Have you checked if and how your library or framework really uses prepared statements, or does it just look like it might be using them?

P.S.: Note that the work-around proposed in the forum link above does not provide the security of prepared statements properly supported by a library.

P.P.S.: To clarify, I haven't demonstrated an SQL injection vulnerability in pyPGSQL. It's not about the performance penalty either. It's about escaping done by the client library (the basic implementation of bind parameters without using the database's support) being a second-rate security solution to explicitly telling the database "here is the code. Now here's the data" (prepared statements). It's about decreasing code complexity and reducing chances for "misunderstandings" (and configuration, e.g., encoding, discrepancies). It's about assurance and choosing safer technologies and architectures.

P(3)S.: Why did I expect prepared statement support? Because the Python DBI 2.0 specification for the "execute" method suggests that implementations should be using prepared statements, at least internally:
            "A reference to the operation will be retained by the
            cursor.  If the same operation object is passed in again,
            then the cursor can optimize its behavior.  This is most
            effective for algorithms where the same operation is used,
            but different parameters are bound to it (many times)."

One more thought is that I should be more positive and congratulate the people working on Python 3 for fixing this long-standing problem. They deserve kudos!

Other cybersecurity legislation in the U.S.


In response to my last post, several people have pointed out to me some other initiatives before Congress. Here are some brief comments on a few of them, based on what is available via the Thomas service. I am not going to provide a section-by-section analysis of any of these.

S.921, the US Information and Communications Enforcement Act of 2009

Introduced by Senator Carper and cosponsored by Senator Burris, this act would modify Title 44 (chapter 35) of the US Code to establish the National Office for Cyberspace within the Executive Office of the President (EOP). The intent is that this office would address "...assured, reliable, secure, and survivable global information and communications infrastructure and related capabilities."

There are several other provisions in the act that make agency heads responsible for security of their systems, requires annual security reviews, requires cooperation with the US-CERT, requires establishment of automated reporting, and that charges the Department of Commerce with setting guidelines and standards but allows agencies to employ more stringent standards.

The director of the office created by this bill does not have a defined reporting chain. However, the office is given explicit responsibility for coordinating policy, consulting with agencies, ad working with OMB. Note that the interaction with OMB is coordination of OMB's actions and is not a role with any direct control.

The authority of this new office would not extend to Defense or any of the DNI's agencies.

There is a very short timeline to produce some initial reports (180 days) on the effects of cost savings by using better security. It might take that long simply to begin to define what to measure!

Every Federal agency would have to appoint a CISO (Chief Information Security Officer) responsible for all the things that a CISO normally does in a large organization, including establishing monitoring and response documentation, training, purchasing, and so on. This would be a massive undertaking for some agencies, even if appropriate budget was allocated (something this bill does not do).

The bill require every agency to have an independent (external) evaluation every year! The cost and effort of such an option would be huge, and it is not clear that it would provide a return equal to cost.

Overall, there are some worthwhile ideas in here, but if passed as is, this would cripple many smaller agencies without sufficient budget, and tie up the rest in lots of red tape.   

S. 1438 Fostering a Global Response to Cyber Attacks Act

Introduced by Senator Gillibrand, this bill would state a "sense of the Senate" and require the Secretary of State to report on efforts to work with other countries on cyber security and response. Section 21 of S.778 provides better coverage of the topic.

S. 946 Critical Electric Infrastructure Protection Act

Introduced by Senator Lieberman with no cosponsors, this bill directs the Secretary of DHS (working with other agencies) to direct a study and report if federally-owned elements of the power grid have been compromised in any way. It further tasks the Federal Electric Reguatory Commission (FERC) to establish interim measures to protect those resources.

It makes the Secretary of Homeland Security responsible for on-going assessments and reporting of critical infrastructure, including the electric infrastructure. Hmmm, no mention of the Secretary of Energy here. This will probably provoke a turf battle if it gets considered at length.

H.R. 2195 by Representative Bennie Thompson and 16 cosponsors is the same bill on the House side.

H.R. 2165 by Rep. John Barrow is related somewhat, in that it designates FERC as responsible for securing the power system. It goes further, however, by giving FERC some emergency regulatory powers under Presidential directive. It also creates yet another class of restricted but unclassified information. Both of those last two points make this a troubling proposal.

H.R. 266 Cybersecurity Education and Enhancement Act of 2009

Introduced by Representative Sheila Jackson-Lee, this act has two major components:

  1. It would task NSF with setting up programs, funded by & coordinated with DHS, for professional education and associated degrees in cyber security. Funding would also be given for equipment for such programs.
  2. It would establish a DHS-run Fellows program to bring state, local, tribal and private sectors officials into the DHS National Cybersecurity Division to become more familiar with the capabilities and missions there.

This would address some real needs in a reasonable way.


Clearly, there is growing interest in cyber within the government, and recognition of some of the weaknesses in procurement, training, response, standards, and information dissemination. However, not all of the bills being proposed really address the underlying problems, and some may cause new problems.   

The legislative process does not lend itself to solutions. The House and Senate deal with issues via an established committee structure, and those committee boundaries don't match cyber, which is a cross-cutting problem. Thus, it is difficult to get a bill started that mandates changes across several Federal agencies and cabinet positions, because the bill would then need to go through a bunch of committees -- and in too many cases there are members of those committees who will feel the need to rewrite the bill. This especially comes into play thinking about the future: if there will be new programs and authorities, it is generally the rule that each committee would like to "own" those activities . Likewise, the members and staff don't like to see any authority taken away from their committees.

This makes it problematic for cyber. It will require thoughtful support across a number of areas. It will require the leadership of both houses of Congress to exert some leadership to ensure that good legislation gets through, without too much unnecessary tweaking along the way.

Let's keep our fingers crossed.

(Oh, and my post about the "cyber cheerleader" caused a reader to remind of Spaf's First Law, articulated over two decades ago:

If you have responsibility for security but have no authority to set rules or punish violators, your own role in the organization is to take the blame when something big goes wrong.

Thus, people who are being approached for the position may not be eager to take it if they understand this. It has been demonstrated for this sort of position before.

Cybersecurity Legislation


Cyber seems to be one of the buzzwords in Washington these days, with the recent botnet attacks generating a lot of extra noise. This has included at least one rather bellicose response from a US Representative who either is reading much more interesting information than the rest of us, or is not reading anything at all.

Meanwhile, in the background, various bits of legislation are being worked on by several committees in both the House and Senate to address various aspects of the perceived problems. Two notable instances are legislation proposed by Senator Rockefeller and others that followed closely after my testimony before their committee. I have heard that at least one of these pieces of proposed legislation is being revised, and will be reintroduced. Back in April, I sent comments on both proposed bills to committee staff, but never heard a response. I hope my input had some impact.

It occurred to me that I did not blog about the legislation or my comments. So, to correct that oversight, you will find the enclosed, which are my original comments with some newer perspective gained over the last few months. You can find the text of these bills via Thomas.

(I will post a follow-up when I see what the revised bills are like.)

The National Cybersecurity Advisor Act of 2009, S. 778

This proposed legislation, cosponsored by Senators Snowe, Bayh and Nelson, was a bit of a puzzle to me when it was introduced. The timing was such that the President's 60-day review report had not yet been delivered, and so it seemed premature to me. However, in retrospect, the 60-day review didn't end up suggesting a powerful office within the EOP for cyber, and so this bill was right on target.

The bill would establish an office of National Cybersecurity Advisor , with the head of that office reporting directly to the President. That person would have authority to hire consultants, consult with any Federal agency, approve clearances of personnel related to cyber, and have access to all classified programs relating to cyber. More importantly, the advisor "...shall review and approve all cybersecurity-related budget requests submitted to the Office of Management and Budget" and would "...serve as the principal advisor to the President for all cybersecurity-related matters." Both of these would be an improvement over the suggestions in the final 60-day review.

The bill has had two readings and has been referred to the Committee on Homeland Security and Governmental Affairs.

(I note that the 60 day review would have been delivered to the President on April 9. It is now more than 3 months later, and still no appointment of the cybersecurity cheerleader proposed by that document.)

The National Cybersecurity Act of 2009, S 773

This was also introduced before the 60-day review was released. It contains 23 sections. It has been read twice and referred to the Committee on Commerce, Science, and Transportation. It also is cosponsored by Snowe, Nelson and Bayh.

Sec. 1: Title And Table Of Contents.

Pro forma material.

Sec 2: Findings

This is a section devoted to bits of information that justify the bill. Several people are cited for things they have said on the topics; I was not one of them, although Purdue was mentioned in point 13, and the PITAC report I helped prepare was listed in point 14.

Sec 3: Cybersecurity Advisory Panel

This section defines the creation of a high-level, Presidential advisory panel. The panel will be composed of individuals from a broad cross-section of society, and will provide the President with advice on strategy, trends, priorities, and civil liberties related to cyber security. The panel will be required to provide a report at least once every 2 years.

This looks to be well-designed and potentially very useful. Panels such as this depend on the alacrity with which a President appoints appropriate members, whether those members actually get something useful done, and whether the President heeds their advice. But at least this framework is off to a good start.

Sec 4. Real-time Cybersecurity Dashboard

The Secretary of Commerce is mandated to develop a "real-time dashboard" within a year. This dashboard is supposed to show the cybersecurity status and vulnerability information of all networks managed by the Department of Commerce.

This is quite puzzling. It isn't clear to me why this is restricted to Commerce, although notes I have from staff indicate that the intent is to serve as a pilot for other parts of government. But that isn't the end of the puzzle. Who is supposed to view this dashboard? What do they do after they see something on it? And what the heck does it really measure? (Hopefully not a dynamic FISMA score!)

Of course, I can't help noting that having one location to collect and display vulnerability information is a very bad idea.

Sec 5. State And Regional Cybersecurity Enhancement Program

This section describes the creation of a set of centers around the country to assist small businesses with cybersecurity. It is modeled on the Hollings Manufacturing Extension Partnership (MEP) and would be run by the Department of Commerce. The centers would receive up to 1/2 of their initial funding from the Federal government, with the rest to come from states, regional groups, and fees paid by members. The centers would provide expertise and resources to small companies.

Although I have some misgivings about this, it is the best suggestion I have seen yet on how to get cybersecurity technology out to small businesses in an affordable manner. I was not familiar with this program and had suggested something similar to our agricultural extension model, so this is in keeping with that. The questions I have are whether these will attract the necessary funding and talent to be viable. But it is probably worth the experiment.

Sec 6. NIST Standards Development And Compliance

This section sets out that, within a year, the Secretary of Commerce will establish a research plan for security metrics, establish a whole set of metrics and compliance measures for vulnerabilities and testing, set all these as standards, and apply them to all vendors and government systems. This will also constrain acceptable configurations, and provide accreditation of suppliers.

Whew! This is way off base. We don't know how to do many of these things, and I fear that setting a deadline will mean that a number of poor standards and requirements will be established. Not only that, having a set of uniform configurations (and required compliance to them) is a sure way to weaken our security rather than strengthen it -- diversity and uncertainty have protective effects when used appropriately. Requiring everyone to code the same way, and configure only approved systems the same way is not going to be helpful -- except to the bad guys.

This is also a good way to kill innovation in an area (software development and security deployment) where innovation is badly needed.   

This is a bad idea.

Sec 7. Licensing And Certification Of Cybersecurity Professionals

This provision requires Commerce to develop a national licensing and certification program for cybersecurity professionals. Within 3 years, it would be unlawful to provide security services to any government or national security system without the certification.

This is worse than section 6! We don't know yet what the appropriate skills are for professionals. In fact, there are a wide range of skills, not all of which are needed by each person.

The result of this, if it gets enacted, is either that we will have a least-common denominator for skills that will get taught by a lot of training organizations that will enrich them but do nothing for the nation, or the bar will be set so high that we will have a shortage of qualified personnel. Either way, it may also stifle enhanced and unconventional training that could produce new talent.

I have been working as an educator in this field for two decades. This section presents an awful idea.

Sec 8. Review Of NTIA Domain Name Contracts

Basically requires the Advisory Panel (Sec 3) to review any contract renewal with ICANN, and gives it veto authority.

Reasonable. it doesn't address some of the problems with ICANN, but it isn't clear that Congress can do that.

Sec 9. Secure Domain Name Addressing System

Within 3 years, the Commerce Department must come up with a strategy and schedule to implement DNSSEC, and the President must require all agencies and departments to follow that plan.

Probably reasonable, and with a more realistic timetable than some of the other sections.

Sec 10. Promoting Cybersecurity Awareness

Basically, the Secretary of Commerce is charged with finding ways to increase public awareness of cybersecurity. Not a bad idea, but the real issue occurs when budgets are allocated. Commerce gets stuck with lots of unfunded mandates, and I don't see this as ranking up there with, say, maintaining the nation's atomic clocks or evaluating the next digital signature standard. So, if the budgets are cramped, this won't happen.

Sec 11. Federal Cybersecurity Research And Development

This directs NSF to provide more funding towards some specific hard research issues (assurance, attribution, insider threat, privacy protection, etc.), and to help ensure that students get some training in secure code production techniques (although that is a somewhat nebulous concept). It also authorizes significant new funding levels for research, establishment of centers, and funding traineeships.

Overall, I think the intent is good. The issue is once again one of appropriations each year to fund these initiatives. if "new" funding is available, that is great. However, if this ends up eating into other research thrusts, it is generally not good for the community as a whole.

It is also the case that when substantial blocks of money are made available, suddenly "experts" come out the woodwork to compete for it. New ideas and new blood are needed in the area, but it is almost certain that a significant part of this will not accomplish what is intended, although what is accomplished may still have value. I would hope that the NSF doesn't try to address this by tying funds to the Centers of Excellence (sic).

Sec 12. Federal Cyber Scholarship-For-Service Program

The NSF SoS program would be expanded in size and scope, and codify it in law. The Scholarship for Service program grew out of an idea I presented to Congress back in 1997. It has functioned well, although it has not attracted large numbers of students, for a variety of reasons. The expansion of the program in this draft bill doesn't really change the nature of the program, so I would be very surprised if the 1000 students per year would actually matriculate. I suppose the numbers might get pumped up if more schools participated, but we don't have the faculty or educational materials nationally to do that. Thus, I have reservations about this, too.

Sec 13. Cybersecurity Competition And Challenge

This would direct NIST to set up national competitions at different levels for cybersecurity. There is also authorization to solicit for and award prize money to winners.

I can see where this might increase interest in the field, and bring more people out to solve problems. However, the majority of challenges held in the field right now are "hacking into the opposing server" challenges, and I have contended over the years that such an approach should not be encouraged. It we are looking for employees of cyber military groups, this might be okay. But hack challenges don't really recognize the well-rounded and adept defenders and researchers. Attack challenges also don't tend to engage women, who are already badly underrepresented in the field.

So, this is another qualified "maybe" section: good intent, but a lot depends on implementation.

Sec 14. Public-Private Clearinghouse

This establishes Commerce as the home of vulnerability and threat information for government systems and critical private infrastructure. Commerce also has to come up with methods and standards for protecting and sharing this information.

  Hmmm, I thought DHS was supposed to be doing all this now?

Sec 15. Cybersecurity Risk Management Report

The President is supposed to come up with a report on the feasibility of a risk and insurance market for cyber risk. The report is also supposed to include the feasibility of including that risk in bond ratings.

I've often said that if we could get the insurance industry engaged, we might well see some progress in private sector security. However, without some liability for companies (above and beyond loss risk) it still might not be enough. This bill doesn't touch the liability issue, which is likely to be a third rail issue for any legislation.

Sec 16. Legal Framework Review And Report

This section of the bill would mandate review of existing law that touches on cyber, and require recommendations for any necessary changes. This includes the ECPA, the Privacy Act, FISMA, and others. This would be a very good idea. The review would be delivered to Congress. At that point, there is no way to predict what might happen, but a review is definitely needed.

Sec 17. Authentication And Civil Liberties Report'

Briefly mandates study of a national identification and authentication program, including the civil liberties issues associated therewith.

This is another touchy topic. There are many groups advocating for strongly authenticated ID, but there are also reasons to proceed with caution. Performing an in-depth study is probably worthwhile, but I'd prefer to see the National Academies tasked with it than an agency of government.

Sec 18. Cybersecurity Responsibilities And Authority

This would give the President authority to disconnect government or critical infrastructure systems in the event of an emergency. it would also grant authority for mapping systems, setting standards, monitoring performance, and other activities to protect and defend national-interest systems. It also allows the President to designate an agency or organization to be in charge during any cyber incident – presumably including Department of Defense agencies.

This has been controversial because of the "disconnect" provision. It isn't clear to me that there are situations that would be helped by a disconnect, although I can certainly imagine some that might be made worse by disconnection. I'm not sure that the current infrastructure would even allow disconnection! So, on balance, if it were left out I don't think it would matter, but it might make some people less nervous.

Most of the other parts of the section seem reasonable.

Sec 19. Quadrennial Cyber Review

Every four years there would need to be a review of cybersecurity posture, strategy, partnerships, threats, and so on. The Advisory Panel (Sec 3) would be involved. "The review shall include a comprehensive examination of the cyber strategy, force structure, modernization plans, infrastructure, budget plan, the Nation's ability to recover from a cyberemergency, and other elements of the cyber program and policies with a view toward determining and expressing the cyber strategy of the United States and establishing a revised cyber program for the next 4 years." Wow!

This is modeled after the Defense Department's review of the same name, I assume. It would be a tremendous amount of work, and might be a huge distraction. However, it also might help to highlight some of the shortfalls and dangers in a way that would be useful for policymakers.

One consideration from the DoD side: structuring reporting in this way tends to move planning from annual or biennial cycles to quadrennial or octennial cycles. In a fast-moving field such as cyber, this might well be counterproductive.

Sec 20. Joint Intelligence Threat Assessment

it states "The Director of National Intelligence and the Secretary of Commerce shall submit to the Congress an annual assessment of, and report on, cybersecurity threats to and vulnerabilities of critical national information, communication, and data network infrastructure."   

Well, that's reasonable. Hmm, where is DHS?

Sec 21. International Norms And Cybersecurity Deterrance Measures

The President is directed to work with foreign governments to increase engagement and cooperation in cybersecurity.   

We can hardly argue with that!

Sec 22. Federal Secure Products And Services Acquisitions Board

This would establish a board to set and review requirements for Federal acquisitions to ensure that cybersecurity standards are met.

My comments on section 6 hold here as well.

Sec 23. Definitions

Assorted definitions to interpret other parts of the bill.


S. 778 seems like a reasonable idea, although it isn't clear that enough responsibility is given to the position. Merging with S773 might be reasonable with many of the tasks in S.773 currently delegated to the President instead delegated to the new position.

S.773 is best where it encourages new development. reporting, education and response. Unfortunately, some of the restrictions and mandates, especially Sections 6 and 7, make the bill more toxic than helpful.

The new funding required to carry everything out would be in the many hundreds of millions of dollars per year. Most of that is explicitly authorized in this legislation, but corresponding appropriation is not a certainty...and given the current economic climate, it is unlikely. Thus, there are some things contained in here that would end up as unfunded mandates on a few agencies (such as NIST) that are already laboring under a huge taskload with insufficient resources.

No mention is made of bolstering law enforcement at any level to help deal with cybersecurity issues. That is unfortunate, because it is one place where some immediate impact could definitely be made. However, given the way this will wend through committees, that is not unexpected. Commerce gets the bill first, so they get the direction.

DHS isn't mentioned anywhere. Again, that may be because of the path the bill will take through committees. However, I can't help but think it also has to do with the way that DHS has screwed up in this whole arena.

Overall, this bill evidences a great deal of careful thought and deep concern. There are many great ideas in here, as well as a few flawed ones. I have my fingers crossed that the rumored revision addresses the flaws and results in something that can get passed into law. Even a pared-down law consisting of sections 3, 5, 9, 10, 11, 12, 16 and 21 would have a lot of positive impact.